查询SQlServer相同表结构差异

USE [数据库名]
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.colorderSELECT * FROM (
SELECT COUNT(1) AS 表1字段数量 FROM #temp1
)a ,
(
SELECT COUNT(1) AS 表2字段数量 FROM #temp2)bSELECT 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.字段名2SELECT 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.字段名2SELECT * FROM #temp_1;
SELECT * FROM #temp_2;DROP TABLE #temp1;DROP TABLE #temp2;DROP TABLE #temp_1;DROP TABLE #temp_2;
GO