用来显示2个数据库中具有不同表名的表的信息以及具有相同表的表结构不同的信息。
/*
Test1
Test3
DataBase2 Test3
*/
with A as
(
select name,type from Test1.sys.objects where type in ('U','V')
),
B as
(
select name,type from Test3.sys.objects where type in ('U','V')
)
select case when A.type is null then B.type else A.type end as Kind, A.name as Test1,B.name as Test3 from A full outer join B
on A.name=b.name and A.type=B.type
where A.name is null or B.name is null
go
with A as
(
select name,type from Test1.sys.objects where type in ('U','V')
),
B as
(
select name,type from Test3.sys.objects where type in ('U','V')
) ,
AWithB as
(
select case when A.type is null then B.type else A.type end as type, A.name as TableName from A inner join B
on A.name=b.name and A.type=B.type
),
----
AC as
(
select obj.name as TableName,obj.type,col.name
from test1.sys.objects obj inner join test1.sys.columns col
on obj.object_id =col.object_id
where obj.type in ('U','V')
),
BC as
(
select obj.name as TableName ,obj.type,col.name
from Test3.sys.objects obj inner join Test3.sys.columns col
on obj.object_id =col.object_id
where obj.type in ('U','V')
),
ACWithBC as
(
select
case when Ac.type is null then BC.type else AC.type end as Type,
case when Ac.TableName is null then BC.TableName else AC.TableName end as TableName,
AC.name as Test1 ,BC.name as Test3
from AC full outer join BC
on AC.name=bC.name and AC.type=BC.type and AC.TableName =BC.TableName
where AC.name is null or BC.name is null
)
select AWithB.TableName ,AWithB.type ,ACWithBC.Test1 ,ACWithBC .Test3 from AWithB left join ACWithBC
on AWithB.type=ACWithBC .Type and AWithB.TableName =ACWithBC.TableName
go
