如何用SQL自动检查不同数据库中表的差异

2021-07-22 15:14:14

问题描述：

工作过程中，不管是什么项目，伴随着项目不断升级版本，对应的项目数据库业务版本也不断升级，数据库出现新增表、修改表、删除表、新增字段、修改字段、删除字段等变化，如果人工检查，数据库表和字段比较多的话，工作量就非常大。

解决方案：
这里为大家分享一个在工作过程中编写的自动检查数据库表结构版本差异的通用脚本，只需要把新旧数据库名称批量替换成实际的名称就可以，支持通过链接服务器跨服务器检查不同服务器的两个数据库表结构差异。

具体脚本：

使用说明：Old数据库为SQL_Road1，New数据库为[localhost].SQL_Road2。根据实际需要批量替换数据库名称，其中[localhost]也可以改成远程数据库IP地址。

sys.objects插入临时表

SELECT
  s.name + '.' + t.name AS TableName,
  t.* INTO #tempTA
FROM
  SQL_Road1.sys.tables t
INNER JOIN SQL_Road1.sys.schemas s
ON s.schema_id = t.schema_id
SELECT
  s.name + '.' + t.name AS TableName,
  t.* INTO #tempTB
FROM
  [localhost].SQL_Road2.sys.tables t
INNER JOIN [localhost].SQL_Road2.sys.schemas s
ON s.schema_id = t.schema_id

sys.columns插入临时表

SELECT
  * INTO #tempCA
FROM
  SQL_Road1.dbo.syscolumns 
SELECT
  * INTO #tempCB
FROM
  [localhost].SQL_Road2.dbo.syscolumns

第一个数据库表和字段

SELECT
  b.TableName AS 表名,
  a.name AS 字段名,
  a.length AS 长度,
  c.name AS 类型 INTO #tempA
FROM
  #tempCA a
INNER JOIN #tempTA b ON b.object_id = a.id
INNER JOIN systypes c ON c.xusertype = a.xusertype
ORDER BY  b.name

第二个数据库表和字段

SELECT
  b.TableName AS 表名,
  a.name AS 字段名,
  a.length AS 长度,
  c.name AS 类型 INTO #tempB
FROM
  #tempCB a
INNER JOIN #tempTB b ON b.object_id = a.id
INNER JOIN systypes c ON c.xusertype = a.xusertype
ORDER BY  b.name

删掉的字段

SELECT  *
FROM
(
  SELECT  * FROM  #tempA
  EXCEPT
  SELECT  * FROM  #tempB
) a;

增加的字段

SELECT  * FROM
(
  SELECT  * FROM  #tempB
  EXCEPT
  SELECT  * FROM  #tempA
) a;

这样我们就将两个数据库中表结构的差异比对出来了，当然这一般在数据同步过程中可能才会用到。

码农公寓

相关文章