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 ?

Cᴏʀʏ · Accepted Answer · 2017-08-31T14:27:35.927

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.

Tks a lot for your help, I'm going to try it. B ut I'm thinking maybe sql have a key that would help without the needing of take the full object definition. — Juan Pablo Gomez, Jun 23 '14 at 22:50
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](http://opendbiff.codeplex.com/) or [many others](http://stackoverflow.com/questions/685053/what-is-best-tool-to-compare-two-sql-server-databases-schema-and-data) that are free. — Cᴏʀʏ, Jun 23 '14 at 22:57
Really apreciate your help. Allready have my own schema comparison your sugestion could speed up it. — Juan Pablo Gomez, Jun 23 '14 at 23:02

SQL Comparing two Stored procedures between databases

1 Answers1