sql server - SQL Comparing two Stored procedures between databases -

sql server - SQL Comparing two Stored procedures between databases -

i have 2 version of same database , need campare objects between them (stored procedures, views etc.)

actually ?m using sqldmo-sqlsmo retrieve text of each object perform text comparison. efective take long time if have more tan 1000+ objects.

my question is. there simple way perform comparing ? maybe md5 key generated on databases ?

why not query definitions straight sql server instead of having overheard of using management objects?

select sysobjects.name [object name] ,(case sysobjects.xtype when 'p' 'stored procedure' when 'tf' 'function' when 'tr' 'trigger' when 'v' 'view' end) [object type] ,syscomments.text [object definition] sysobjects bring together syscomments on sysobjects.id = syscomments.id sysobjects.xtype in ('p', 'tf', 'tr', 'v') , sysobjects.category = 0

i ran against database have here. returned ~1,500 definitions in 0.6 seconds. if run against each server collect definitions, can comparing on object name, type, , definition in 1 big loop.

this expensive operation memory-wise should pretty quick. of cpu time spent doing actual string comparisons.

as other questions "key" available can compare, there's no such hash or equivalent know of. in sys.syscomments table have ctext column (raw bytes of sql definition) , text column text representation of bytes.

fyi: if have visual studio 2010 premium or higher, there's built-in schema compare tool you. there's open dbdiff or many others free.

sql sql-server sqldmo sql-smo