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
enjoy!!!
I hope it add with compare stored procedure either
Thanks!
Satheesh,
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
few questions:
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?
Error:
———————————————-
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 ?
Hi,
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?