Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Using multiple databases INFORMATION_SCHEMA without an EXEC Statement

Answered Using multiple databases INFORMATION_SCHEMA without an EXEC Statement

  • Tuesday, February 12, 2013 1:46 AM
     
      Has Code
    Sign In to Vote
    0

    Hi all,

    I recently created a script for a single database that had the purpose of comparing column names between it's '_Archive' and associated parent tables (to ensure all data was being archived by the trigger)

    I now want to expand this to work on ALL the databases in my server instance, and I can achieve that already using a temp table to store database names, and a while loop and exec statement to execute the string query for every database name in the table (could have also used a cursor).

    Personally, I HATE this method, but have not found another means to achieve this.  Here is the query I want to apply to ALL databases without the use of EXEC.

    SELECT T.TABLE_CATALOG AS DBName, T.TABLE_NAME AS ParentTable, A.TABLE_NAME AS ArchiveTable, MA.Summary AS RowsMissingFromArchiveTable, MP.Summary AS RowsInArchiveTableNotPresentInParentTable
	FROM INFORMATION_SCHEMA.TABLES T
	INNER JOIN INFORMATION_SCHEMA.TABLES A
		ON A.TABLE_NAME = T.TABLE_NAME + '_Archive'
	CROSS APPLY (
		SELECT C.COLUMN_NAME + ', '
		FROM  INFORMATION_SCHEMA.COLUMNS C
		WHERE C.TABLE_NAME = T.TABLE_NAME
			AND C.COLUMN_NAME NOT IN (
				SELECT C1.COLUMN_NAME
				FROM INFORMATION_SCHEMA.COLUMNS C1
				WHERE C1.TABLE_NAME = A.TABLE_NAME)
		ORDER BY C.COLUMN_NAME
		FOR XML PATH ('')
	) AS MA (Summary)--RowsMissingFromArchiveTable
	CROSS APPLY (
		SELECT C.COLUMN_NAME + ', '
		FROM INFORMATION_SCHEMA.COLUMNS C
		WHERE C.TABLE_NAME = A.TABLE_NAME
			AND C.COLUMN_NAME NOT IN (
				SELECT C1.COLUMN_NAME
				FROM INFORMATION_SCHEMA.COLUMNS C1
				WHERE C1.TABLE_NAME = T.TABLE_NAME)
		ORDER BY C.COLUMN_NAME
		FOR XML PATH ('')
	) AS MP (Summary) --RowsInArchiveTableNotPresentInParentTable
	ORDER BY T.TABLE_CATALOG, T.TABLE_NAME

    Any help would be appreciated,  or advice on where my understanding of MSSQL is insufficient would be great also.

    Thanks!

     

All Replies

  • Tuesday, February 12, 2013 4:52 AM
    Moderator
     
     
    Sign In to Vote
    0

    Take a look at this blog post as how I would approach this problem from T-SQL point of view

    How to get information about all databases without a loop

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, February 12, 2013 5:05 AM
     
     Answered Has Code
    Sign In to Vote
    0

    There is no way to get all table or column names in one query.  You need to one way or another handle each database one at a time.  This could be done with a cursor, or a loop thru master.sys.databases and then doing an EXEC using each database name.  Or you could build a query for each database in the result window, copy and paste the result into another query window and then run those queries.  But there is no way to do this as one query without doing something like that.

    One way to do this would be to use the sp_MSForEachDB stored procedure.  You would still be using a cursor (because that stored procedure has a cursor), but the code looks cleaner.  It should be noted that sp_MSForEachDB is an undocumented stored procedure and therefore there is a possibility that Microsoft could change or remove it in a future release.  But it's so widely used that I don't think there is any real risk.  So if I were doing this, I would use that stored proc to get all column names in all databases and put them in a temp table.  Then use that temp table to build another temp table that contains all table names in all databases.  I also changed the code to handle schema names.  Then the code could look like

    Create Table #Tables(DatabaseName sysname, SchemaName sysname, TableName sysname, Primary Key (DatabaseName, SchemaName, TableName));
    Create Table #Columns(DatabaseName sysname, SchemaName sysname, TableName sysname, ColumnName sysname, Primary Key (DatabaseName, SchemaName, TableName, ColumnName));
Exec sp_MSForeachdb 'Use [?] Insert #Columns(DatabaseName, SchemaName, TableName, ColumnName)  
    Select ''?'', Schema_Name(o.schema_id), o.name, c.name 
	From sys.columns c 
	Inner Join sys.objects o On o.object_id = c.object_id';
Insert #Tables(DatabaseName, SchemaName, TableName) Select Distinct DatabaseName, SchemaName, TableName From #Columns;

SELECT T.DatabaseName AS DBName, T.SchemaName, T.TABLENAME AS ParentTable, A.TABLENAME AS ArchiveTable, 
  Stuff(MA.Summary, 1, 2, '') AS RowsMissingFromArchiveTable, 
  Stuff(MP.Summary, 1, 2, '') AS RowsInArchiveTableNotPresentInParentTable
	FROM #Tables T
	INNER JOIN #Tables A
		ON A.DatabaseName = T.DatabaseName And A.SchemaName = T.SchemaName And A.TABLENAME = T.TABLENAME + '_Archive'
	CROSS APPLY (
		SELECT ', ' + C.COLUMNNAME
		FROM  #Columns C
		WHERE C.DatabaseName = T.DatabaseName And C.SchemaName = T.SchemaName And C.TABLENAME = T.TABLENAME
			AND C.COLUMNNAME NOT IN (
				SELECT C1.COLUMNNAME
				FROM #Columns C1
				WHERE C1.DatabaseName = A.DatabaseName And C1.SchemaName = A.SchemaName And C1.TABLENAME = A.TABLENAME)
		ORDER BY C.COLUMNNAME
		FOR XML PATH ('')
	) AS MA (Summary)--RowsMissingFromArchiveTable
	CROSS APPLY (
		SELECT ', ' + C.COLUMNNAME
		FROM  #Columns C
		WHERE C.DatabaseName = A.DatabaseName And C.SchemaName = A.SchemaName And C.TABLENAME = A.TABLENAME
			AND C.COLUMNNAME NOT IN (
				SELECT C1.COLUMNNAME
				FROM #Columns C1
				WHERE C1.DatabaseName = T.DatabaseName And C1.SchemaName = T.SchemaName And C1.TABLENAME = T.TABLENAME)
		ORDER BY C.COLUMNNAME
		FOR XML PATH ('')
	) AS MP (Summary) --RowsInArchiveTableNotPresentInParentTable
	ORDER BY T.DatabaseName, T.SchemaName, T.TableName



go
Drop Table #Tables;
Drop Table #Columns;

    Tom