Database comparison
-
Hi,
I need to compare tables and table structures between 2 databases.
Both db's are on the same server. Running Windows 2003 Server and SQL 2000. Since I have over 1,200 tables in each, I can't do this manually.
Haven't been able to find anything in books online about this. I was looking for an sp that would do a comparison on tables and columns.
My best guess is a cross database query using a subquery to find differences. Assume db names "Prod" and "Test". Where "Test" has the changes.
I have an idea but am stuck on syntax and where to look.
For column changes
i.e. Test.SYSCOLUMNS.NAME not in (Prod.SYSCOLUMNS.NAME FROM Prod.SYSCOLUMNS WHERE SYSOBJECTS.ID = SYSCOLUMNS.ID AND ?????)
For new tables:
repeat above but Test.SYSOBJECTS.NAME not in (PROD.SYSOBJECTS.NAME.....
Any help would be greatly appreciated.
Thanks,
Greg
Greg H -
We use a third party utility to do this, why reinvent the wheel (actually we're using 2 to decide which is best).
Red-Gate's SQL Compare and Innovartis' DB Ghost.
Both are more than adequate for what you need to do.
-
Here's the link for db Ghost :
-
Mike and Remi,
Thanks for the fast response. I'll check into both of those options. Definitely don't have the time to re-invent any wheels.
Greg
Greg H -
Especially not that wheel
. -
But if you want you can check the sysobjects and syscolumns tables in your databases to find a difference if you do not want to spend money for a 3rd party tool.
As an example for the difference of user tables:
select O.name as DB1_TableName_Not_Existing_in_DB2
from DB1..sysobjects O
where not exists (select 1 from DB2..sysobjects O1
where O.name = O1.name
and O1.type = 'U')
and O.type = 'U'
select O.name as DB2_TableName_Not_Existing_in_DB1
from DB2..sysobjects O
where not exists (select 1 from DB1..sysobjects O1
where O.name = O1.name
and O1.type = 'U')
and O.type = 'U'
You can do the same with the columns of your tables, the users, the object owners, procedures...
Bye
Gabor -
The quick answer is to get SQL Compare or a similar tool.
Another solution is to use Enterprise Manager to script both databases to a file, and run the files through WinDiff (in Visual Studio). If you select the options to script indexes and foreign keys, the comparison will be pretty thorough.
A quick-and-dirty T-SQL solution might be:
use db1
select * into #db1 from information_schema.columns
use db2
select * into #db2 from information_schema.columns
create clustered index IX_db1 on #db1 (table_schema, table_name, column_name)
create clustered index IX_db2 on #db2 (table_schema, table_name, column_name)
select * from #db1 a full join #db2 b on a.table_schema = b.table_schema and a.table_name = b.table_name and a.column_name = b.column_name
where a.table_name is null or b.table_name is null
or a.data_type <> b.data_type
You could include character_maximum_length, is_nullable, numeric_precision etc. if you wanted to get into that level of detail.
Then you can start working on comparing indexes, foreign keys, constraints, etc. Or work harder on getting SQL Compare.
-
We use Redgate SQL Compare.
You should know that there are several other tools out there in addition to the ones that others have already mentioned. Embarcadero has a pretty good tool called Change Manager. It's not as fast as SQL Compare, but does a whole lot more than SQL Compare. It's really a schema management suite that you can use as a compare utility. Quest Central also has a compare tool built in. I haven't used it yet so I can't pass judgement, good or bad.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply