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
If you see this text, you are using RSS to read or turn to "a Tree-blog park," The original address: http://www.cnblogs.com/atree/p/db-compare-structure.html
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,匹配情况=casewhen 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.textfrom [‘[email protected]+‘]..sysobjects a,[‘[email protected]+‘]..syscomments bwhere 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.textfrom [‘[email protected]+‘]..sysobjects a,[‘[email protected]+‘]..syscomments bwhere 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 nullor b.name is nullor 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) --要比较的数据库名2ascreatetable#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 aleft join ‘[email protected]+‘..systypes b on a.xtype=b.xusertypeinner 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.idleft join sys.extended_properties gONa.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 aleft join ‘[email protected]+‘..systypes b on a.xtype=b.xusertypeinner 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.idleft join sys.extended_properties gONa.ID=g.major_id AND a.COLID=g.minor_idorder by a.id,a.colorder‘)--and not exists(select 1 from #tb2 where 表名2=a.表名1)select比较结果=casewhena.表名1 isnullandb.序号=1 then‘库1缺少表：‘+b.表名2whenb.表名2 isnullanda.序号=1 then‘库2缺少表:‘+a.表名1whena.字段名 isnullandexists(select1 from#tb1 where表名1=b.表名2) then‘库1 [‘+b.表名2+‘] 缺少字段：‘+b.字段名whenb.字段名 isnullandexists(select1 from#tb2 where表名2=a.表名1) then‘库2 [‘+a.表名1+‘] 缺少字段：‘+a.字段名whena.标识 <>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 afulljoin#tb2 b ona.表名1=b.表名2 anda.字段名=b.字段名wherea.表名1 isnullora.字段名 isnullorb.表名2 isnullorb.字段名 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 MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7600.16385
Microsoft. NET Framework 2.0.50727.4984
Operating system 6.1.7600
Two databases compare and contrast view stored procedures and table structure differences
