sql server表结构对比

【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;