0

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 ?

Juan Pablo Gomez
  • 5,203
  • 11
  • 55
  • 101

2 Answers2

3

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.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • 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
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.

Ji Zhang
  • 21
  • 1