/*--數(shù)據(jù)結(jié)構(gòu)比較

	比較兩個(gè)數(shù)據(jù)庫(kù)中的視圖/存儲(chǔ)過(guò)程的結(jié)構(gòu)(結(jié)構(gòu)比較,不是功能比較)

--鄒建 2004.07(引用請(qǐng)保留此信息)--*/

/*--調(diào)用示例

	--調(diào)用
	exec p_compdb 'pubs','northwind'
--*/
create proc p_compdb
@db1 sysname,	--第一個(gè)庫(kù)
@db2 sysname	--第二個(gè)庫(kù)
as
exec('
select 類型=case isnull(a.xtype,b.xtype) when ''V'' then ''視圖'' else ''存儲(chǔ)過(guò)程'' end
	,匹配情況=case 
		when a.name is null then ''庫(kù) ['+@db1+'] 中無(wú)''
		when b.name is null then ''庫(kù) ['+@db2+'] 中無(wú)''
		else ''結(jié)構(gòu)不同'' end
	,對(duì)象名稱=isnull(a.name,b.name)
from(
	select a.name,a.xtype,b.colid,b.text
	from ['+@db1+']..sysobjects a,['+@db1+']..syscomments b
	where a.id=b.id and a.xtype in(''V'',''P'') and a.status>=0
)a full join(
	select a.name,a.xtype,b.colid,b.text
	from ['+@db2+']..sysobjects a,['+@db2+']..syscomments b
	where a.id=b.id and a.xtype in(''V'',''P'') and a.status>=0
)b on a.name=b.name and a.xtype=b.xtype and a.colid=b.colid
where a.name is null
	or b.name is null
	or isnull(a.text,'''')<>isnull(b.text,'''')
group by a.name,b.name,a.xtype,b.xtype
order by 類型,匹配情況,對(duì)象名稱')
go