比較兩個的表結構差異

/*--比較兩個資料庫的表結構差異
 
--*/
/*--呼叫示例
 
 execp_comparestructure'xzkh_model','xzkh_new'
--*/

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_comparestructure]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_comparestructure]
GO

createprocp_comparestructure
@dbname1varchar(250), --要比較的資料庫名1
@dbname2varchar(250) --要比較的資料庫名2
as
createtable#tb1(表名1varchar(250),欄位名varchar(250),序號int,標識bit,主鍵bit,型別varchar(250),
 佔用位元組數int,長度int,小數位數int,允許空bit,預設值varchar(500),欄位說明varchar(500))

createtable#tb2(表名2varchar(250),欄位名varchar(250),序號int,標識bit,主鍵bit,型別varchar(250),
 佔用位元組數int,長度int,小數位數int,允許空bit,預設值varchar(500),欄位說明varchar(500))

--得到資料庫1的結構
exec('insertinto#tb1SELECT
 表名=d.name,欄位名=a.name,序號=a.colid,
 標識=casewhena.status=0x80then1else0end,
 主鍵=casewhenexists(SELECT1FROM'+@dbname1+'..sysobjectswherextype=''PK''andnamein(
  SELECTnameFROM'+@dbname1+'..sysindexesWHEREindidin(
   SELECTindidFROM'+@dbname1+'..sysindexkeysWHEREid=a.idANDcolid=a.colid
  )))then1else0end,
 型別=b.name, 佔用位元組數=a.length,長度=a.prec,小數位數=a.scale, 允許空=a.isnullable,
 預設值=isnull(e.text,''''''),欄位說明=isnull(g.[value],'''''')
FROM'+@dbname1+'..syscolumnsa
 leftjoin'+@dbname1+'..systypesbona.xtype=b.xusertype
 innerjoin'+@dbname1+'..sysobjectsdona.id=d.id andd.xtype=''U''and d.name<>''dtproperties''
 leftjoin'+@dbname1+'..syscommentseona.cdefault=e.id
 leftjoin'+@dbname1+'..syspropertiesgona.id=g.idanda.colid=g.smallid 
orderbya.id,a.colorder')

--得到資料庫2的結構
exec('insertinto#tb2SELECT
 表名=d.name,欄位名=a.name,序號=a.colid,
 標識=casewhena.status=0x80then1else0end,
 主鍵=casewhenexists(SELECT1FROM'+@dbname2+'..sysobjectswherextype=''PK''andnamein(
  SELECTnameFROM'+@dbname2+'..sysindexesWHEREindidin(
   SELECTindidFROM'+@dbname2+'..sysindexkeysWHEREid=a.idANDcolid=a.colid
  )))then1else0end,
 型別=b.name, 佔用位元組數=a.length,長度=a.prec,小數位數=a.scale, 允許空=a.isnullable,
 預設值=isnull(e.text,''''''),欄位說明=isnull(g.[value],'''''')
FROM'+@dbname2+'..syscolumnsa
 leftjoin'+@dbname2+'..systypesbona.xtype=b.xusertype
 innerjoin'+@dbname2+'..sysobjectsdona.id=d.id andd.xtype=''U''and d.name<>''dtproperties''
 leftjoin'+@dbname2+'..syscommentseona.cdefault=e.id
 leftjoin'+@dbname2+'..syspropertiesgona.id=g.idanda.colid=g.smallid 
orderbya.id,a.colorder')
--andnotexists(select1from#tb2where表名2=a.表名1)
select比較結果=casewhena.表名1isnullandb.序號=1then'庫1缺少表：'+b.表名2
  whenb.表名2isnullanda.序號=1then'庫2缺少表:'+a.表名11