比較兩個資料庫的表結構差異

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[p_comparestructure]    Script Date: 02/09/2017 15:39:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--比較兩個資料庫的表結構差異--*/
  
/*--調用示例 
exec p_comparestructure 'DBNAME1','DBNAME2' 
exec p_comparestructure 'DBNAME2','DBNAME3' 
--*/
create proc [dbo].[p_comparestructure] 
@dbname1 varchar(250),--要比較的資料庫名1 
@dbname2 varchar(250) --要比較的資料庫名2 
as
create table #tb1(表名1 varchar(250),欄位名 varchar(250),序號 int,標識 bit,主鍵 bit,類型 varchar(250), 
占用位元組數 int,長度 int,小數位數 int,允許空 bit,預設值 sql_variant,欄位說明 sql_variant) 
  
create table #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 '+@dbname1+'..sysobjects where xtype=''PK'' and parent_obj=a.id 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, 
類型=b.name,占用位元組數=a.length,長度=a.prec,小數位數=a.scale,允許空=a.isnullable, 
預設值=isnull(e.text,''''),欄位說明=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 sys.extended_properties g 
ON 
a.ID=g.major_id AND a.COLID=g.minor_id
order 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 '+@dbname2+'..sysobjects where xtype=''PK'' and parent_obj=a.id 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, 
類型=b.name,占用位元組數=a.length,長度=a.prec,小數位數=a.scale,允許空=a.isnullable, 
預設值=isnull(e.text,''''),欄位說明=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 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 比較結果=case when a.表名1 is null and b.序號=1 then N'庫1缺少表：'+b.表名2 
when b.表名2 is null and a.序號=1 then N'庫2缺少表:'+a.表名1 
when a.欄位名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then N'庫1 ['+b.表名2+'] 缺少欄位：'+b.欄位名 
when b.欄位名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then N'庫2 ['+a.表名1+'] 缺少欄位：'+a.欄位名 
when a.標識 <>b.標識 then N'標識不同'
when a.主鍵 <>b.主鍵 then N'主鍵設置不同'
when a.類型 <>b.類型 then N'欄位類型不同'
when a.占用位元組數 <>b.占用位元組數 then N'占用位元組數'
when a.長度 <>b.長度 then N'長度不同'
when a.小數位數 <>b.小數位數 then N'小數位數不同'
when a.允許空 <>b.允許空 then N'是否允許空不同'
when a.預設值 <>b.預設值 then N'預設值不同'
when a.欄位說明 <>b.欄位說明 then N'欄位說明不同'
else '' end, 
* 
from #tb1 a 
full join #tb2 b on a.表名1=b.表名2 and a.欄位名=b.欄位名 
where a.表名1 is null or a.欄位名 is null or b.表名2 is null or b.欄位名 is null
or a.標識 <>b.標識 or a.主鍵 <>b.主鍵 or a.類型 <>b.類型 
or a.占用位元組數 <>b.占用位元組數 or a.長度 <>b.長度 or a.小數位數 <>b.小數位數 
or a.允許空 <>b.允許空 or a.預設值 <>b.預設值 or a.欄位說明 <>b.欄位說明 
order by isnull(a.表名1,b.表名2),isnull(a.序號,b.序號)--isnull(a.欄位名,b.欄位名)