Compare table structure differences for two databases

/*--comparing table structure differences of two databases--*/

/*--Invocation Example
exec p_comparestructure ' DBNAME1 ', ' DBNAME2 '
exec p_comparestructure ' DBNAME2 ', ' DBNAME3 '
--*/
ALTER proc [dbo]. [P_comparestructure]
@dbname1 varchar (,--) database name to compare 1
@dbname2 varchar (250)--the database name to compare 2
As
CREATE table #tb1 (table name 1 varchar (250), field name varchar (250), ordinal int, identity bit, primary key bit, type varchar (250),
occupies bytes int, length int, decimal place int, allow null bit, default value sql_variant, 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),
occupies bytes int, length int, decimal place 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,
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, takes up bytes =a.length, length =a.prec, number of decimal digits =a.scale, allows null =a.isnullable,
Default value =isnull (E.text, ""), field Description =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 ')

--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, takes up bytes =a.length, length =a.prec, number of decimal digits =a.scale, allows null =a.isnullable,
Default value =isnull (E.text, ""), field Description =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 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 2
When B. Table Name 2 is null and a. Ordinal =1 then ' Library 2 missing table: ' +a. Table Name 1
When a. Field names are 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 names are 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. Identity then ' identity different '
When a. primary key <>b. Primary key Then ' primary key set different '
When a. Type <>b. Type then ' field type different '
When a. Occupies a number of bytes <>b. Takes bytes and then ' takes up bytes '
When a. length <>b. Length Then ' length different '
When a. Number of decimal digits <>b. Decimal place Then ' decimal place different '
When a. Allow null <>b. Allow NULL then ' Allow null difference '
When a. Default value <>b. Default Then ' default value is different '
When a. Field description <>b. field description Then ' field description different '
Else ' End,
*
From #tb1 A
Full 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. Field name is null or B. Table Name 2 is null or B. Field name is null
or a. Identity <>b. Identifies or a. primary key <>b. primary key or a. Type <>b. type
Or a. occupies bytes <>b. The number of bytes or a. length <>b. Length or a. Number of decimal digits <>b. Decimal digits
or a. Allow null <>b. Null or A. 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. Serial number)--isnull (A. Field name, B. Field name)

Compare table structure differences for two databases