2004281410@SQL-SERVER-对比数据库表架构差异 | LOFT.CLUEE.TECH

--取出数据库1中的表名，字段名 以及 数据类型
USE XXX; 
GO
SELECT a.name AS TableName,
       b.name AS ColName,
       c.name AS DataType
INTO #tmptable
FROM sysobjects a --存储库中所有的数据表信息
    INNER JOIN syscolumns b
        ON a.id = b.id --数据库的各表的所有字段
    INNER JOIN systypes c
        ON b.xtype = c.xtype --数据类型
WHERE a.xtype = 'U'; --类型为 U 的是用户表
GO

--取出对比数据库的表名 字段名以及数据类型
USE YYY;

SELECT a.name AS TableName,
       b.name AS ColName,
       c.name AS DataType
INTO #tmptable1
FROM sysobjects a
    INNER JOIN syscolumns b
        ON a.id = b.id
    INNER JOIN systypes c
        ON c.xtype = b.xtype
WHERE a.xtype = 'U';
GO

--比较两表中的数据差异
GO

SELECT *
FROM #tmptable a
WHERE NOT EXISTS
(
    SELECT * FROM #tmptable1 b WHERE a.TableName = b.TableName AND [a].[ColName]=[b].[ColName]
)
ORDER BY TableName;