Compare table structure differences for two databases

Compare | data | database
/*--Comparison of table structure differences between two databases

--*/
/*--Call Example

exec p_comparestructure ' Xzkh_model ', ' xzkh_new '
--*/

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_comparestructure] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_comparestructure]
Go

Create proc P_comparestructure
@dbname1 varchar (250),--database name to compare 1
@dbname2 varchar (250)--database name to compare 2
As
CREATE table #tb1 (table name 1 varchar (250), field name varchar (250), ordinal int, identifier bit, primary key bit, type varchar (250),
Occupy bytes int, length int, decimal number int, allow NULL bit, default value varchar (500), field description varchar (500))

CREATE table #tb2 (table name 2 varchar (250), field name varchar (250), ordinal int, identifier bit, primary key bit, type varchar (250),
Occupy bytes int, length int, decimal number int, allow NULL bit, default value varchar (500), field description varchar (500))

--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 ' + @dbname1 + '. sysobjects where xtype= ' PK ' and name in (
SELECT name from ' + @dbname1 + '. sysindexes WHERE indid in (
SELECT indid from ' + @dbname1 + '. Sysindexkeys WHERE id = a.id and colid=a.colid
)) then 1 else 0 end,
Type =b.name, occupy byte number =a.length, length =a.prec, decimal digit =a.scale, allow empty =a.isnullable,
Default value =isnull (E.text, ' ""), field Description =isnull (G.[value], "" ")
From ' + @dbname1 + '. Syscolumns A
Left Join ' + @dbname1 + '. Systypes B on A.xtype=b.xusertype
INNER JOIN ' + @dbname1 + '. sysobjects d on a.id=d.id and d.xtype= ' U ' and d.name<> ' dtproperties '
Left Join ' + @dbname1 + '. syscomments E on A.cdefault=e.id
Left Join ' + @dbname1 + '. Sysproperties g on A.id=g.id and A.colid=g.smallid
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 ' + @dbname2 + '. sysobjects where xtype= ' PK ' and name in (
SELECT name from ' + @dbname2 + '. sysindexes WHERE indid in (
SELECT indid from ' + @dbname2 + '. Sysindexkeys WHERE id = a.id and colid=a.colid
)) then 1 else 0 end,
Type =b.name, occupy byte number =a.length, length =a.prec, decimal digit =a.scale, allow empty =a.isnullable,
Default value =isnull (E.text, ' ""), field Description =isnull (G.[value], "" ")
From ' + @dbname2 + '. Syscolumns A
Left Join ' + @dbname2 + '. Systypes B on A.xtype=b.xusertype
INNER JOIN ' + @dbname2 + '. sysobjects d on a.id=d.id and d.xtype= ' U ' and d.name<> ' dtproperties '
Left Join ' + @dbname2 + '. syscomments E on A.cdefault=e.id
Left Join ' + @dbname2 + '. Sysproperties g on A.id=g.id and A.colid=g.smallid
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 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. Identity then ' identity is different '
When a. primary key <>b. Primary key Then ' primary key setting is different '
When a. Type <>b. Type then ' field type is different '
When a. Occupies the number of bytes <>b. Bytes Then ' bytes occupied '
When a. length <>b. Length Then ' length is different '
When a. Decimal places <>b. Decimal places, then ' decimal places are different '
When a. Allow null <>b. Allow NULL then ' Allow null difference '
When a. Default value <>b. Default value Then ' default value is different '
When a. Field description <>b. field description Then ' field description is 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. Identifies <>b. Identifies or a. primary key <>b. primary key or a. Type <>b. type
Or A. Occupies a number of bytes <>b. bytes or a. length <>b. Length or a. Scale <>b. Number of decimal places
Or a. Allow empty <>b. Allow null or a. Default value <>b. The 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