SQL Comparing two Stored procedures between databases

Question

I have two version of same database and need to campare some objects between them (stored procedures, views etc.)

Actually ?m using SQLDMO-SQLSMO to retrieve the Text of each object then I perform a text comparison. This is efective but take a long time most if I have more tan 1000+ objects.

My question is. There is a simple way to perform this comparison ? maybe an MD5 key generated on the databases ?

Answer 1

Why not just query for the definitions directly from SQL server instead of having the overhead of using the management objects?

SELECT
    sysobjects.name AS [Object Name]
    ,(CASE sysobjects.xtype
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'TF' THEN 'Function'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'V' THEN 'View' END) AS [Object Type]
    ,syscomments.text AS [Object Definition]
FROM 
    sysobjects
JOIN
    syscomments
    ON
    sysobjects.id = syscomments.id
WHERE 
    sysobjects.xtype in ('P', 'TF', 'TR', 'V')
    AND 
    sysobjects.category = 0

I ran that against a database I have here. It returned ~1,500 definitions in 0.6 seconds. If you run that against each server to collect all the definitions, you can do a comparison on object name, type, and definition in one big loop.

This will be an expensive operation memory-wise but should be pretty quick. Most of the CPU time will be spent doing the actual string comparisons.

As for your other questions about a "key" available that you can compare, there's no such hash or equivalent that I know of. In the sys.syscomments table you have the ctext column (raw bytes of the SQL definition) and the text column which is the text representation of those bytes.

FYI: If you have Visual Studio 2010 Premium or higher, there's a built-in Schema Compare tool that will do this all for you. There's also Open DBDiff or many others that are free.

Answer 2

I am using checksum(text) to compare. But if there is a carriage return or space at very beginning of procedures, you will get unexpected results comparing with the Visual studio schema comparison tool.