Cross-Database information_schema Joins in SQL Server

Question

I am attempting to provide a general solution for the migration of data from one schema version to another. A problem arises when the column data type from the source schema does not match that of the destination. I would like to create a query that will perform a preliminary compare on the columns data types to return which columns need to be fixed before migration is possible.

My current approach is to return the table and column names from information_schema.columns where DATA_TYPE's between catalogs do not match. However, querying information_schema directly will only return results from the catalog of the connection.

Has anyone written a query like this?

Answer 1

I have always been in the fortunate position to have Red Gate Schema compare which i think would do what you ask. Cheap at twice the price!

Answer 2

I do this by querying the system tables directly. Look into the syscolumns and sysobjects tables. You can also join across linked servers too

select t1.name as tname,c1.name as cname from adventureworks.dbo.syscolumns c1 join adventureworks.dbo.sysobjects t1 on c1.id = t1.id where t1.type = 'U' order by t1.name,c1.colorder