How can I compare tables in two different databases using SQL?

I开发者_JAVA百科'm trying to compare the schemas of two tables that exist in different databases. So far, I have this query

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('table1')

The only thing is that I don't know how to use the sys.columns to reference a database other than the one that the query is connected to. I tried this

SELECT * FROM db.sys.columns WHERE object_id = OBJECT_ID('table1')

but it didn't find anything.

I'm using SQL Server 2005

Any suggestions? thanks!

Take a look at redgate's SQL Compare.

To answer your specific question, you need to fully qualify the table reference.

SELECT * FROM db.sys.columns WHERE object_id = OBJECT_ID('db.SchemaName.table1')

Late one but hopefully useful.

Even though chama asked for SQL solutions I’d still recommend using a third party tools such as ApexSQL Diff or tools from Red Gate Joe already mentioned (I’ve used both and they worked great).

Reason is that query for comparing two tables using information schema has to be quite complex in order to catch all differences.

Note that all of the examples mentioned here only cover columns but none of the queries shown here will show the difference between nvarchar(20) and nvarchar(50) or difference in foreign keys or indexes or….

Short answer is yes – this is possible using information schema views but it can be rather complex if you want to compare every detail in those two tables.

all you need is to specify the DB name and shcema when calling OBJECT_ID function, like:

SELECT * 
FROM DB_NAME.sys.columns 
WHERE object_id = OBJECT_ID('DB_NAME.SHCEMA_NAME.table1')

Try the information_schema. eg:

select * 
from 
   db1.information_schema.columns col1
   join db2.information_schema.columns col2
     on col1.table_catalog = col2.table_catalog
     and col1.table_schema = col2.table_schema
     and col1.column_name = col2.column_name
...

The information_schema simplifies sticking together the information from all the sys.columns,sys.objects etc. It exists automatically in your DB. I think its actually an ISO standard thing, so should work on various DB systems.

More information about the information_schema can be found here

Comparing whether the object or columns exists in both schemas is only a tiny bit of the solution. What if they exist in both databases but are different?

For my bsn ModuleStore project, I implemented a scripting routine which actually scripts most DB objects including table and view columns, indexes, namespaces etc. as XML using T-SQL code only. This may be a good place to start. You can find it on Google code, and the file in question (which generates the SQL query for dumping the object schema to XML) is here.

Code -

drop table #a
drop table #b


select *
into #a
from [databasename1].information_schema.columns a
--where table_name = 'aaa'


select *
into #b
from [databasename2].information_schema.columns b -- add linked server name and db as needed


--where table_name = 'bbb'


select distinct( a.table_name), b.TABLE_SCHEMA+ '.' + (b.table_name) TableName,b.TABLE_CATALOG DatabaseName
from #a a


right join #b b  


on a.TABLE_NAME = b.TABLE_NAME and a.TABLE_SCHEMA = b.TABLE_SCHEMA
where a.table_name is null-- and a.table_name not like '%sync%'

Just in case you are using MS VS 2015 (Community is a free download). The SOL Server tools includes a Schema Comparison tool. "SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions".

This is a GPL Java program I wrote for comparing data in any two tables, with a common key and common columns, across any two heterogeneous databases using JDBC: https://sourceforge.net/projects/metaqa/

It intelligently forgives (numeric, string and date) data type differences by reducing them to a common format. The output is a sparse tab delimited file with .xls extension for use in a spreadsheet.

The program ingests SQL that is used to produce a source table that can be compared with the target table. The target table SQL can be generated automatically. The target table is read one row at a time and therefore should be indexed on the common key.

It detects missing rows on either side and common keyed rows with other optional column differences. Obviously the meta data can be accessed by SQL so whether your concern is with the data, or with the meta-data, it will still work.

This is very powerful in a data migration or System migration project, and also for auditing interfaces. You will be astounded at the number of errors it detects. Minimal false positives still do occur.

Informix, Oracle and SQL-Server were the first JDBC targets and you can extend that list if desired.