Compare database objects
I had posted a database object comparison script in technet gallery long back. I will be posting more updates here.
At present this scripts compares tables, indexes and foreign keys.
sample usage : Once you deploy the stored procedure you can just run this command to compare two databases
exec SP_Comparedb db1,db2
Download the code sp_compareDB.zip
10 thoughts on “Compare database objects”
I hope it add with compare stored procedure either
This looks like cool script.Do you or some have have similar script to use for DB2 LUW databases ?
best website solutions
The error you are getting is because you have some tables with foreign keys pointing to a non-existent table or a table that does not have a match on the target DB. To avoid this add this where clause in the dynamic string (line 304 more or less) :
where object_name(FK.referenced_object_id,db_id(”’+@TargetDB+”’)) is not null
i am getting this error
Msg 515, Level 16, State 2, Procedure sp_CompareDb, Line 275
Cannot insert the value NULL into column ‘FK_TABLE’, table ‘tempdb.dbo.#FKLIST_SOURCE
Are are getting this error for all the databases? I tried to reproduce the issue, but I couldn’t really do it. Could you send me the reproduction steps then I can have a look at it
Hi thanks for this useful script. I have a
Any plans to add SP’s and triggers?
Also this may not really related to your sp, but I ran a test using SSMS Generate script wizard (2008r2) creating a db script from a db, changed the db name to create a dup db.
Then ran your compare sp between the two,
the compare listed several PK row deltas which when examined don’t appear to be actual deltas. If I create a 2nd dup from the generated script then compare with the first dup db your sp lists no differences. Any idea what this may indicate?
Comparing databases factoring and webfactoring
Getting table and column list!
Getting index list!
Getting foreign key list!
Mensagem 515, Nível 16, Estado 2, Procedimento sp_CompareDb, Linha 275
Não é possível inserir o valor NULL na coluna ‘PK_TABLE’, tabela ‘tempdb.dbo.#FKLIST_SOURCE______________________________________________________________________________________________________000000000025’; a coluna não permite nulos. Falha em INSERT.
A instrução foi finalizada.
Print column mismatches!
Print index mismatches!
Print key mismatches!
Could You help me ?
You did a great job with this script, but could You tell me what should I change with You script to compare BASE_1 with BASE_2 and get as a result the table with DIFFERENCE between tables schema?
For example if I have a table “TABLE1” in both databases and in BASE_1 is additional column “ADDITIONAL_COLUMN” and in BASE_2 there is “ADDITIONAL_COLUMN”. I would like to receive as a result the DIFFERENCE for example BASE_2 Missing column “ADDITIONAL_COLUMN”
Is it hard to receive ??
I will need the same with the indexes …
Could You help me to change this script?