Tuesday, February 12, 2013 1:46 AM
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.
Tuesday, February 12, 2013 4:52 AMModerator
Take a look at this blog post as how I would approach this problem from T-SQL point of view
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, February 12, 2013 5:05 AM
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;