34,590
社区成员
发帖
与我相关
我的任务
分享
/**
* 名称:两个SQLServer数据库比较
* 用途:比较数据库内对象(表、视图、函数、存储过程、触发器、约束等)、字段(数据类型及长度)
* 假设:库TESTDB22222与TESTDB11111是一个项目不同时期的版本数据库,由于整合的需要,那么下面的功能可能有用
* 使用:将TESTDB22222、TESTDB11111库的名称替换成你需要对比的两个即可F5
* 作者:黄顺龙
* 联系:huangshunlong@139.com
*/
create table #type(type nvarchar(50) collate Latin1_General_CI_AS_KS_WS,info nvarchar(50))
insert #type values('AF','聚合函数(CLR)');
insert #type values('C','CHECK约束');
insert #type values('D','DEFAULT(约束或独立)');
insert #type values('F','FOREIGNKEY约束');
insert #type values('PK','PRIMARYKEY约束');
insert #type values('P','SQL存储过程');
insert #type values('PC','程序集(CLR)存储过程');
insert #type values('FN','SQL标量函数');
insert #type values('FS','程序集(CLR)标量函数');
insert #type values('FT','程序集(CLR)表值函数');
insert #type values('R','规则(旧式,独立)');
insert #type values('RF','复制筛选过程');
insert #type values('SN','同义词');
insert #type values('SQ','服务队列');
insert #type values('TA','程序集(CLR)DML触发器');
insert #type values('TR','SQLDML触发器');
insert #type values('IF','SQL内联表值函数');
insert #type values('TF','SQL表值函数');
insert #type values('U','表(用户定义类型)');
insert #type values('UQ','UNIQUE约束');
insert #type values('V','视图');
insert #type values('X','扩展存储过程');
insert #type values('IT','内部表');
--找出缺失对象(表、视图、函数、存储过程、触发器、约束等)
select 'TESTDB11111缺少对象【'+b.info+'】:'+a.name from TESTDB11111.sys.objects a,#type b where a.name='reg_ownership_app' and cast(a.type as nvarchar(50))=cast(b.type as nvarchar(50)) and a.name not in(
select c.name from taw.sys.objects c where c.name='reg_ownership_app'
)
union all
select 'TESTDB22222缺少对象【'+b.info+'】:'+a.name from taw.sys.objects a,#type b where a.name='reg_ownership_app' and cast(a.type as nvarchar(50))=cast(b.type as nvarchar(50)) and a.name not in(
select c.name from TESTDB11111.sys.objects c where c.name='reg_ownership_app'
)
--找出缺失表
--select 'TESTDB22222无表:'+name from TESTDB11111.sys.tables where type='U' and name not in (
-- select name from taw.sys.tables
--)
--union all
--select 'TESTDB11111无表:'+name from taw.sys.tables where type='U' and name not in (
-- select name from TESTDB11111.sys.tables
--)
union all
--找出缺失字段
select 'TESTDB11111..'+a.name+'表中缺少字段:'+b.name
from TESTDB11111.sys.columns b,TESTDB11111.sys.tables a
where a.object_id= b.object_id and a.name='reg_ownership_app' and b.name='reg_ownership_app'
and a.name in(
select e.name from taw.sys.tables e
)
and b.name not in (
select name from taw.sys.columns c where c.object_id=(select top 1 d.object_id from taw.sys.tables d where d.name=a.name)
)
union all
select 'taw..'+a.name+'表中缺少字段:'+b.name
from taw.sys.columns b,taw.sys.tables a
where a.object_id= b.object_id and a.name='reg_ownership_app' and b.name='reg_ownership_app'
and a.name in(
select e.name from TESTDB11111.sys.tables e
)
and b.name not in (
select name from TESTDB11111.sys.columns c where c.object_id=(select top 1 d.object_id from TESTDB11111.sys.tables d where d.name=a.name)
)
union all
--找出差异字段(数据类型及长度不一致的情况)
select 'TESTDB11111.dbo.'+a.name+'.'+b.name+'字段的数据类型或长度与TESTDB22222库中的描述不一致' from TESTDB11111.sys.tables a,TESTDB11111.sys.columns b
where a.object_id= b.object_id
and a.name in(
select c.name from TESTDB11111.sys.tables c
)
and b.name in(
select d.name from TESTDB11111.sys.columns d where d.object_id in(
select e.object_id from TESTDB11111.sys.tables e where e.name=a.name
)
)
and (
b.user_type_id !=(select top 1 f.user_type_id from taw.sys.columns f where f.name=b.name and f.object_id = (select top 1 g.object_id from taw.sys.tables g where g.name=a.name))
or b.max_length !=(select top 1 f.max_length from taw.sys.columns f where f.name=b.name and f.object_id = (select top 1 g.object_id from taw.sys.tables g where g.name=a.name))
)
union all
select 'taw.dbo.'+a.name+'.'+b.name+'字段的数据类型或长度与TESTDB11111库中的描述不一致'
from taw.sys.tables a,taw.sys.columns b
where a.object_id= b.object_id
and a.name in(
select c.name from taw.sys.tables c
)
and b.name in(
select d.name from taw.sys.columns d where d.object_id in(
select e.object_id from taw.sys.tables e where e.name=a.name
)
)
and (
b.user_type_id !=(select top 1 f.user_type_id from TESTDB11111.sys.columns f where f.name=b.name and f.object_id = (select top 1 g.object_id from TESTDB11111.sys.tables g where g.name=a.name))
or b.max_length !=(select top 1 f.max_length from TESTDB11111.sys.columns f where f.name=b.name and f.object_id = (select top 1 g.object_id from TESTDB11111.sys.tables g where g.name=a.name))
)
drop table #type
--sys.objects.type 类型枚举值如下:
--AF = 聚合函数 (CLR)
--C = CHECK 约束
--D = DEFAULT(约束或独立)
--F = FOREIGN KEY 约束
--PK = PRIMARY KEY 约束
--P = SQL 存储过程
--PC = 程序集 (CLR) 存储过程
--FN = SQL 标量函数
--FS = 程序集 (CLR) 标量函数
--FT = 程序集 (CLR) 表值函数
--R = 规则(旧式,独立)
--RF = 复制筛选过程
--SN = 同义词
--SQ = 服务队列
--TA = 程序集 (CLR) DML 触发器
--TR = SQL DML 触发器
--IF = SQL 内联表值函数
--TF = SQL 表值函数
--U = 表(用户定义类型)
--UQ = UNIQUE 约束
--V = 视图
--X = 扩展存储过程
--IT = 内部表