【1】库与库的表结构对比
ALTER PROC [dbo].[SP_CompareStructure]
@dbname1 VARCHAR(100), --要比较的数据库名1
@dbname2 VARCHAR(100) --要比较的数据库名2
AS
/*
--脚本原作者：邹建
--脚本更新：Windy QQ1420110988
--EXEC SP_CompareStructure 'db1', 'db2'
*/
create table #tb1(对象名1 nvarchar(100),对象类型1 varchar(10),序号1 integer,字段名1 nvarchar(100),字段类型1 varchar(20),长度1 integer,精度1 integer)
create table #tb2(对象名2 nvarchar(100),对象类型2 varchar(10),序号2 integer,字段名2 nvarchar(100),字段类型2 varchar(20),长度2 integer,精度2 integer)
--得到数据库1的结构
EXEC('INSERT INTO #tb1
SELECT 对象名=d.name,对象类型=d.xtype,序号=a.colid,字段名=a.name,字段类型=b.name,长度=a.prec,精度=a.scale
FROM '+@dbname1+'..syscolumns a
left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname1+'..sysobjects d on a.id=d.id and d.xtype IN (''U'',''V'') and d.name <>''dtproperties''
ORDER BY a.id,a.colorder')
--得到数据库2的结构
EXEC('INSERT INTO #tb2
SELECT 对象名=d.name,对象类型=d.xtype,序号=a.colid,字段名=a.name,字段类型=b.name,长度=a.prec,精度=a.scale
FROM '+@dbname2+'..syscolumns a
left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname2+'..sysobjects d on a.id=d.id and d.xtype IN (''U'',''V'') and d.name <>''dtproperties''
ORDER BY a.id,a.colorder')
SELECT
比较结果=CASE WHEN a.对象名1 IS NULL AND NOT EXISTS (SELECT 1 FROM #tb1 WHERE 对象名1=b.对象名2 AND 对象类型1=b.对象类型2) THEN (CASE WHEN b.对象类型2='V' THEN '库1缺少视图：' ELSE '库1缺少表：' END)+b.对象名2
WHEN b.对象名2 IS NULL AND NOT EXISTS (SELECT 1 FROM #tb2 WHERE 对象名2=a.对象名1 AND 对象类型2=a.对象类型1) THEN (CASE WHEN a.对象类型1='V' THEN '库2缺少视图：' ELSE '库2缺少表：' END)+a.对象名1
WHEN a.字段名1 IS NULL AND EXISTS (SELECT 1 FROM #tb1 WHERE 对象名1=b.对象名2 AND 对象类型1=b.对象类型2) THEN '库1 ['+b.对象名2+'] 缺少字段：'+b.字段名2
WHEN b.字段名2 IS NULL AND EXISTS (SELECT 1 FROM #tb2 WHERE 对象名2=a.对象名1 AND 对象类型2=a.对象类型1) THEN '库2 ['+a.对象名1+'] 缺少字段：'+a.字段名1
WHEN a.字段类型1 <>b.字段类型2 THEN '字段类型不同'
WHEN a.长度1 <>b.长度2 THEN '长度不同'
WHEN a.精度1 <>b.精度2 THEN '精度不同'
ELSE '' END,
结果类型=CASE WHEN a.对象名1 IS NULL AND NOT EXISTS (SELECT 1 FROM #tb1 WHERE 对象名1=b.对象名2 AND 对象类型1=b.对象类型2) THEN (CASE WHEN b.对象类型2='V' THEN 'view' ELSE 'table' END)
WHEN b.对象名2 IS NULL AND NOT EXISTS (SELECT 1 FROM #tb2 WHERE 对象名2=a.对象名1 AND 对象类型2=a.对象类型1) THEN (CASE WHEN a.对象类型1='V' THEN 'view' ELSE 'table' END)
WHEN a.字段名1 IS NULL AND EXISTS (SELECT 1 FROM #tb1 WHERE 对象名1=b.对象名2 AND 对象类型1=b.对象类型2) THEN 'fieldname'
WHEN b.字段名2 IS NULL AND EXISTS (SELECT 1 FROM #tb2 WHERE 对象名2=a.对象名1 AND 对象类型2=a.对象类型1) THEN 'fieldname'
WHEN a.字段类型1 <>b.字段类型2 THEN 'fieldtype'
WHEN a.长度1 <>b.长度2 THEN 'fieldlength'
WHEN a.精度1 <>b.精度2 THEN 'fieldprecision'
ELSE '' END,
*
FROM #tb1 a FULL JOIN #tb2 b ON a.对象名1=b.对象名2 AND a.字段名1=b.字段名2
WHERE a.对象名1 IS NULL OR a.字段名1 IS NULL OR b.对象名2 IS NULL OR b.字段名2 IS NULL
OR a.字段类型1<>b.字段类型2 OR a.长度1<>b.长度2 OR a.精度1<>b.精度2
ORDER BY ISNULL(a.对象类型1,b.对象类型2),ISNULL(a.对象名1,b.对象名2),ISNULL(a.字段名1,b.字段名2)
DROP TABLE #tb1,#tb2
其他参考
--当然用 sql compare更好/*
使用说明：Old数据库为DB_V1，New数据库为[localhost].DB_V2。根据实际需要批量替换数据库名称
脚本来源：https://www.cnblogs.com/zhang502219048/p/11028767.html
*/
-- sysobjects插入临时表
select s.name + '.' + t.name as TableName, t.* into #tempTA
from DB_V1.sys.tables t
inner join DB_V1.sys.schemas s on s.schema_id = t.schema_id
select s.name + '.' + t.name as TableName, t.* into #tempTB
from [localhost].DB_V2.sys.tables t
inner join [localhost].DB_V2.sys.schemas s on s.schema_id = t.schema_id
-- syscolumns插入临时表
select * into #tempCA from DB_V1.dbo.syscolumns
select * into #tempCB from [localhost].DB_V2.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;
