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!!!

10 thoughts on “Compare database objects”

  1. 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

  2. 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

    1. 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

  3. 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?

  4. 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!
    ———————————————-

  5. 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?

Leave a Reply to Earl Cancel reply

Your email address will not be published. Required fields are marked *