查询SQlServer相同表结构差异
时间：2021-07-01 10:21:17
帮助过：2人阅读
[数据库名]
GO
DECLARE @TableName1 NVARCHAR(
100);
DECLARE @TableName2 NVARCHAR(
100);
DECLARE @DataName1 NVARCHAR(
100);
DECLARE @DataName2 NVARCHAR(
100);
SET @DataName1=‘库1‘;
SET @DataName2=‘库2‘;
SET @TableName1=‘表1‘;
SET @TableName2=‘表2‘;
SET @TableName2=@TableName1;
SELECT
(case when a.colorder
=1 then d.name
else null end) 表名1,
a.name 字段名1,
(case when (
SELECT count(
*)
FROM sysobjects
WHERE (name
in (
SELECT name
FROM sysindexes
WHERE (id
= a.id)
AND (indid
in
(SELECT indid
FROM sysindexkeys
WHERE (id
= a.id)
AND (colid
in
(SELECT colid
FROM syscolumns
WHERE (id
= a.id)
AND (name
= a.name)))))))
AND (xtype
= ‘PK‘))
>0 then ‘√‘ else ‘‘ end) 主键1,b.name 类型1,
COLUMNPROPERTY(a.id,a.name,
‘PRECISION‘)
as 长度1
,1 AS aa
INTO #temp1
FROM syscolumns a
left join systypes b
on a.xtype
=b.xusertype
inner join sysobjects d
on a.id
=d.id
and d.xtype
=‘U‘ and d.name
<>‘dtproperties‘
left join 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
left join sys.extended_properties f
on d.id
=f.class
and f.minor_id
=0
where b.name
is not null AND (
SELECT COUNT(
1)
FROM Master..SysDatabases sysA
WHERE sysA.name
=@DataName1 )
>0 AND d.name
=@TableName1
--WHERE d.name=‘要查询的表‘ --如果只查询指定表,加上此条件
order by a.name,a.id,a.colorder
SELECT
(case when a.colorder
=1 then d.name
else null end) 表名2,
a.name 字段名2,
(case when (
SELECT count(
*)
FROM sysobjects
WHERE (name
in (
SELECT name
FROM sysindexes
WHERE (id
= a.id)
AND (indid
in
(SELECT indid
FROM sysindexkeys
WHERE (id
= a.id)
AND (colid
in
(SELECT colid
FROM syscolumns
WHERE (id
= a.id)
AND (name
= a.name)))))))
AND (xtype
= ‘PK‘))
>0 then ‘√‘ else ‘‘ end) 主键2,b.name 类型2,
COLUMNPROPERTY(a.id,a.name,
‘PRECISION‘)
as 长度2
,1 AS aa
INTO #temp2
FROM syscolumns a
left join systypes b
on a.xtype
=b.xusertype
inner join sysobjects d
on a.id
=d.id
and d.xtype
=‘U‘ and d.name
<>‘dtproperties‘
left join 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
left join sys.extended_properties f
on d.id
=f.class
and f.minor_id
=0
where b.name
is not null AND (
SELECT COUNT(
1)
FROM Master..SysDatabases sysA
WHERE sysA.name
=@DataName2 )
>0 AND d.name
=@TableName2
--WHERE d.name=‘要查询的表‘ --如果只查询指定表,加上此条件
order by a.name,a.id,a.colorder
SELECT * FROM (
SELECT COUNT(
1)
AS 表1字段数量
FROM #temp1
)a ,
(
SELECT COUNT(
1)
AS 表2字段数量
FROM #temp2
)b
SELECT COUNT(
1)
FROM #temp1 a
LEFT JOIN #temp2 b
ON a.字段名1
=b.字段名2
WHERE a.长度1
!=ISNULL(b.长度2,
‘‘)
OR a.类型1
!=ISNULL(b.类型2,
‘‘);
SELECT COUNT(
1)
FROM #temp1 a
RIGHT JOIN #temp2 b
ON a.字段名1
=b.字段名2
WHERE ISNULL(a.长度1,
‘‘)
!=b.长度2
or b.类型2
!=ISNULL(a.类型1,
‘‘);
SELECT a.字段名1,b.字段名2,a.类型1,b.类型2,a.长度1,b.长度2
INTO #temp_1
FROM #temp1 a
LEFT JOIN #temp2 b
ON a.字段名1
=b.字段名2
SELECT a.字段名1,b.字段名2,a.类型1,b.类型2,a.长度1,b.长度2
INTO #temp_2
FROM #temp1 a
RIGHT JOIN #temp2 b
ON a.字段名1
=b.字段名2
SELECT * FROM #temp_1;
SELECT * FROM #temp_2;
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp_1;
DROP TABLE #temp_2;
GO
查询SQlServer相同表结构差异
标签：
