Compare Two Sql Databases
I'm trying to compare the schemas of two tables that exist in different databases. So far, I have this query
Compare Two Databases Sql Developer
Nov 16, 2011 - Red-Gate's SQL data comparison tool is my second alternative. 3 time's faster comparison and synchronization of table data in your SQL Server databases.
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!
7 Answers
Take a look at redgate's SQL Compare.
To answer your specific question, you need to fully qualify the table reference.
Late one but hopefully useful.
Compare Two Databases Sql Server 2014
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: List of anne of green gables movies.
Try the information_schema. eg:
Sql Vs Access
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
So stop flipping and fluffing your pillow – try the Sobakawa Cloud Pillow for night after night of restful sleep. The pillow company dalton ga.
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.
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'.
