2005191550@SQL数据库表结构对比 | LOFT.CLUEE.TECH

--取出数据库1中的表名，字段名 以及 数据类型
USE [KGKXDB_GuangHong];
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 [KGKXDB-13-JieShiDa]
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;

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

DROP TABLE [#tmptable];
DROP TABLE [#tmptable1];


--删除表
--DECLARE @delete_sql NVARCHAR(MAX) = ''
--SELECT @delete_sql += ('
--DROP TABLE '+name+';') FROM sysobjects WHERE xtype='u' AND name LIKE 'GpsCar_%'
----PRINT @delete_sql
--EXEC (@delete_sql)