CREATE PROC p_compdb@db1 SYSNAME,--First library @db2 SYSNAME--second library asexec (' SELECT type =case ISNULL (a.xtype,b.xtype) when ' V ' Then ' view ' ' ELSE ' ' stored procedure ' END, matching case =case when A.name was NULL then ' library [' [email protected]+ '] without ' W HEN B.name is NULL and the library [' [email protected]+ '] has no ' ELSE ' structure different ' END, object name =isnull (A.name,b.name) from ( SELECT a.name,a.xtype,b.colid,b.text from [' [email protected]+ ']. SYSOBJECTS a,[' [email protected]+ ']. syscomments B WHERE a.id=b.id and A.xtype in ("V", "P") and a.status>=0) A full JOIN (SELECT a.name,a.xtype,b.co Lid,b.text from [' [email protected]+ ']. SYSOBJECTS a,[' [email protected]+ ']. syscomments b WHERE a.id=b.id and A.xtype in ("V", "P") and a.status>=0) B on A.name=b.name and A.xtype=b.xtype and A.colid=b.colidwhere a.name is null or b.name is null or ISNULL (A.text, "') <>isnull (B.text, '") GROUP by A.N Ame,b.name,a.xtype,b.xtypeorder by type, Match case, object name ') GO
Compare the structure of views/stored procedures in two databases (structural comparisons, not functional comparisons)