(reproduced) Two databases compare and contrast view stored procedures and table structure differences

One, view and stored procedure comparison

The principle uses the system table "sysobjects" and the system table "syscomments" to compare views and stored procedures in the database. system table "sysobjects" has been described in detail before, it is interesting to see: SQL Server System table sysobjects introduction and use

Code

/*--调用示例 exec p_compdb ‘DBNAME1‘,‘DBNAME2‘ exec p_compdb ‘DBNAME2‘,‘DBNAME3‘ --*/CREATEproc p_compdb @db1 sysname, --第一个库 @db2 sysname --第二个库 asexec(‘ select 类型=case isnull(a.xtype,b.xtype) when ‘‘V‘‘ then ‘‘视图‘‘ else ‘‘存储过程‘‘ end ,匹配情况=case when a.name is null then ‘‘库 [‘[email protected]+‘] 中无‘‘ when b.name is null then ‘‘库 [‘[email protected]+‘] 中无‘‘ else ‘‘结构不同‘‘ end ,对象名称=isnull(a.name,b.name),a.text as atext, b.text as btextfrom( 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.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 )b on a.name=b.name and a.xtype=b.xtype and a.colid=b.colid where a.name is null or b.name is null or isnull(a.text,‘‘‘‘) <>isnull(b.text,‘‘‘‘) --group by a.name,b.name,a.xtype,b.xtype --order by 类型,匹配情况,对象名称‘)

"Execution Results"

Comparison of data table structure

"Principle" uses the system table "sysobjects", "sysindexes", "Sysindexkeys", "syscomments", "Sysclumns", "systypes", "extended_properties", Compare the table structure in the database. (more system tables are involved.) Don't introduce them. directly on the code. ）

Code

/*--比较两个数据库的表结构差异--*/ /*--调用示例 exec p_comparestructure ‘DBNAME1‘,‘DBNAME2‘ exec p_comparestructure ‘DBNAME2‘,‘DBNAME3‘ --*/createproc p_comparestructure @dbname1 varchar(250),--要比较的数据库名1 @dbname2 varchar(250) --要比较的数据库名2 ascreatetable#tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250), 占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)  createtable#tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250), 占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)  --得到数据库1的结构 exec(‘insert into #tb1 SELECT 表名=d.name,字段名=a.name,序号=a.colid, 标识=case when a.status=0x80 then 1 else 0 end, 主键=case when exists(SELECT 1 FROM ‘[email protected]+‘..sysobjects where xtype=‘‘PK‘‘ and parent_obj=a.id and name in ( SELECT name FROM ‘[email protected]+‘..sysindexes WHERE indid in( SELECT indid FROM ‘[email protected]+‘..sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end, 类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable, 默认值=isnull(e.text,‘‘‘‘),字段说明=isnull(g.[value],‘‘‘‘) FROM ‘[email protected]+‘..syscolumns a left join ‘[email protected]+‘..systypes b on a.xtype=b.xusertype inner join ‘[email protected]+‘..sysobjects d on a.id=d.id  and d.xtype=‘‘U‘‘ and  d.name <>‘‘dtproperties‘‘ left join ‘[email protected]+‘..syscomments e on a.cdefault=e.id left join sys.extended_properties g ON a.ID=g.major_id AND a.COLID=g.minor_idorder by a.id,a.colorder‘)  --得到数据库2的结构 exec(‘insert into #tb2 SELECT 表名=d.name,字段名=a.name,序号=a.colid, 标识=case when a.status=0x80 then 1 else 0 end, 主键=case when exists(SELECT 1 FROM ‘[email protected]+‘..sysobjects where xtype=‘‘PK‘‘ and parent_obj=a.id and name in ( SELECT name FROM ‘[email protected]+‘..sysindexes WHERE indid in( SELECT indid FROM ‘[email protected]+‘..sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end, 类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable, 默认值=isnull(e.text,‘‘‘‘),字段说明=isnull(g.[value],‘‘‘‘) FROM ‘[email protected]+‘..syscolumns a left join ‘[email protected]+‘..systypes b on a.xtype=b.xusertype inner join ‘[email protected]+‘..sysobjects d on a.id=d.id  and d.xtype=‘‘U‘‘ and  d.name <>‘‘dtproperties‘‘ left join ‘[email protected]+‘..syscomments e on a.cdefault=e.id left join sys.extended_properties g ON a.ID=g.major_id AND a.COLID=g.minor_id order by a.id,a.colorder‘) --and not exists(select 1 from #tb2 where 表名2=a.表名1) select比较结果=casewhena.表名1 isnull andb.序号=1 then‘库1缺少表：‘+b.表名2 whenb.表名2 isnullanda.序号=1 then‘库2缺少表:‘+a.表名1 whena.字段名 isnullandexists(select1 from#tb1 where表名1=b.表名2) then‘库1 [‘+b.表名2+‘] 缺少字段：‘+b.字段名 when b.字段名 isnullandexists(select1 from#tb2 where表名2=a.表名1) then‘库2 [‘+a.表名1+‘] 缺少字段：‘+a.字段名 when a.标识 <>b.标识 then‘标识不同‘whena.主键 <>b.主键 then‘主键设置不同‘whena.类型 <>b.类型 then‘字段类型不同‘whena.占用字节数 <>b.占用字节数 then‘占用字节数‘whena.长度 <>b.长度 then‘长度不同‘whena.小数位数 <>b.小数位数 then‘小数位数不同‘whena.允许空 <>b.允许空 then‘是否允许空不同‘whena.默认值 <>b.默认值 then ‘默认值不同‘whena.字段说明 <>b.字段说明 then‘字段说明不同‘else‘‘end, * from#tb1 a fulljoin#tb2 b on a.表名1=b.表名2 anda.字段名=b.字段名 wherea.表名1 isnullora.字段名 isnullorb.表名2 isnullor b.字段名 isnullora.标识 <>b.标识 ora.主键 <>b.主键 ora.类型 <>b.类型 ora.占用字节数 <>b.占用字节数 ora.长度 <>b.长度 ora.小数位数 <>b.小数位数 ora.允许空 <>b.允许空 ora.默认值 <>b.默认值 ora.字段说明 <>b.字段说明 orderbyisnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名) go

"Execution Results"

PS: Please use the system Administrator (sysadmin) role account for SQL execution above. Other roles I have not tried, there is time to try. When I used an account test that mapped only the library "owner" permission, I reported the following error: The EXECUTE permission on object ' p_compdb ' (Database ' master ', schema ' dbo ') was rejected.

When writing, this article uses the SQL SERVER 2005 version and does not guarantee that other versions will work correctly. If the other version has problems, you can leave a message, study together. The debug environment details are as follows:

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Component (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML3.0 4.0 6.0
Microsoft Internet Explorer8.0.7600.16385
Microsoft. NET Framework2.0.50727.4984
Operating system 6.1.7600

(reproduced) Two databases compare and contrast view stored procedures and table structure differences