How to compare across two databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to compare across two databases

Author

Topic

Prestidigitator
Starting Member

16 Posts

Posted - 2007-12-13 : 15:13:11

I have two databases - version 1 and version 2

I have the same table in both versions but the number of columns has changed from version 1 to version 2.

I can get the list of columns with:

select colid, name, col_text  from _SCHEMA
where table_name like  
'tableA' order by colid

How can I use something like this to compare tableA in dbo.1 to tableA in dbo.2 and just return the columns that exist in dbo.2 that aren't in dbo.1?

I thought something like this might do it, but I get major errors in execution:

use dbo.2 select colid, name, col_text from _SCHEMA
where table_name like  
'tableA' and 
name not in (use dbo.1 select name from _SCHEMA
where table_name like  
'tableA')
order by colid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 19:31:23

use INFORMATION_SCHEMA.COLUMNS to compare

KH
[spoiler]Time is always against us[/spoiler]

Prestidigitator
Starting Member

16 Posts

Posted - 2007-12-13 : 19:47:01

How is that different than _SCHEMA

And it doesn't do anything for checking one database against the other...

Am I missing something?

Prestidigitator
Starting Member

16 Posts

Posted - 2007-12-13 : 20:10:09

Someone internally pointed me to INFORMATION_SCHEMA.COLUMNS as well and helped me put it together like this:


select	A.table_catalog,
	A.table_name,
	A.column_name,
	B.table_catalog,
	B.table_name,
	B.column_name
from	DatabaseA.INFORMATION_SCHEMA.COLUMNS as A
full outer join	DatabaseB.INFORMATION_SCHEMA.COLUMNS as B
	on	A.table_name = B.table_name
	and	A.column_name = B.column_name
where	coalesce(A.table_name, B.table_name) in ('table1', 'table2')
and		(
	B.column_name is null
or	A.column_name is null
)
order by coalesce(A.table_name, B.table_name),
	coalesce(A.ordinal_position,B.ordinal_position)

Thought I would post this in case anyone else was looking for this sort of thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources