Compare tables in two databases

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

/*--Compare table structure differences for two databases--*//*--call example exec p_comparestructure ' DBNAME1 ', ' DBNAME2 ' exec p_comparestructure ' DBNAME2 ', ' DBNAME3 '--*/create proc p_comparestructure@dbname1 varchar (,--) The database name to compare 1@dbname2 varchar (250)--the database name to compare 2ascreate table # TB1 (table name 1 varchar (250), field name varchar (250), ordinal int, identity bit, primary key bit, type varchar (250), byte-count int, length int, decimal number int, allow NULL bit, default value Sql_vari Ant, field description sql_variant) CREATE TABLE #tb2 (table name 2 varchar (250), field name varchar (250), ordinal int, identity bit, primary key bit, type varchar (250), byte count int, length int, decimal number int, allow NULL bit, default value sql_variant, field description sql_variant)--Get the structure of database 1 exec (' INSERT INTO #tb1 Select Table name =d.name, field name =a.name, Ordinal =a.colid, identifying =case when a.status=0x80 then 1 else 0 end, primary key =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, type =b.name, bytes =a.length, length =a.prec, number of decimal digits =a. scale, allow null =a.isnullable, default value =isnull (E.text, ""), field Description =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 ')--get the structure of database 2 exec (' INSERT INTO #tb2 Select Table name =d.name, field name =a.name, ordinal =a.colid, identify =case when a.status=0x80 then 1 Else 0 end, primary key =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, type =b.name, number of bytes =a.length, length =a.prec, scale =a.scale, allow null =a . isnullable, default value =isnull (E.text, ""), Field description =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 table name 2=a. Table Name 1) Select comparison results =case when a. Table name 1 is null and B. Ordinal =1 Then ' library 1 missing Table: ' +b. Table name 2when B. Table name 2 is null and a. Ordinal =1 then ' Library 2 missing table: ' +a. Table name 1when A. Field name is null and exists (select 1 from #tb1 where table name 1=b. Table Name 2 Then ' library 1 [' +b. Table name '] missing field: ' +b. field name when B. Field name is null and exists (select 1 from #tb2 where table name 2=a. Table Name 1) then ' Library 2 [' +a. Table name 1+ ']  Missing field: ' +a. Field name when a. Identity <>b. Identify then ' identify different ' when a. primary key <>b. Primary key Then ' primary key set different ' when a. Type <>b. Type then ' field type different ' when A. The number of bytes <>b. Takes up bytes Then ' takes up bytes ' when a. Length <>b. Length Then ' length different ' when a. Scale <>b. Decimal place Then ' Decimal place different ' when a. Allow null <>b. Allow NULL then ' If NULL is allowed ' when a. Default value <>b. Default value Then ' default value is different ' when a. Field description <>b. field description Then ' field description  Different ' Else ' end,*from #tb1 afull join #tb2 B on a. Table name 1=b. Table Name 2 and a. Field name =b. field name where a. Table name 1 is null or a. The field name is null or B. Table Name 2 is Null or B. The field name is Nullor a. Identity <>b. Identifies the or a. Primary key <>b. The primary key or a. Type <>b. Type or a. takes up bytes <>b. The number of bytes or a. Length &lt ; >b. length or a. Scale <>b. Decimal place or A. Allow null <>b. The default value <>b. Default value or a. Field description <>b. field Description ORDER by IsNull (A.  Table name 1,b. Table Name 2), IsNull (a. Ordinal, B. Ordinal)--isnull (A. Field name, B. Field name) 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 Components (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 systems   6.1.7600