trackbacks-0
一、视图和存储过程比较
【原理】利用系统表&sysobjects"和系统表&syscomments&，将数据库中的视图和存储过程进行对比。系统表"sysobjects"之前有详细介绍过，有兴趣可以看看：
如果你看到这段文字，说明您正使用RSS阅读或转自《一棵树-博客园》，原文地址：
/*--调用示例
exec p_compdb 'DBNAME1','DBNAME2'
exec p_compdb 'DBNAME2','DBNAME3'
CREATE proc p_compdb
@db1 sysname, --第一个库
@db2 sysname --第二个库
select 类型=case isnull(a.xtype,b.xtype) when ''V'' then ''视图'' else ''存储过程'' end
,匹配情况=case
when a.name is null then ''库 ['+@db1+'] 中无''
when b.name is null then ''库 ['+@db2+'] 中无''
else ''结构不同'' end
,对象名称=isnull(a.name,b.name),a.text as atext, b.text as btext
select a.name,a.xtype,b.colid,b.text
from ['+@db1+']..sysobjects a,['+@db1+']..syscomments b
where a.id=b.id and a.xtype in(''V'',''P'') and a.status&=0
)a full join(
select a.name,a.xtype,b.colid,b.text
from ['+@db2+']..sysobjects a,['+@db2+']..syscomments b
where a.id=b.id and a.xtype in(''V'',''P'') and a.status&=0
)b on a.name=b.name and a.xtype=b.xtype and a.colid=b.colid
where a.name is null
or b.name is null
or isnull(a.text,'''') &&isnull(b.text,'''')
--group by a.name,b.name,a.xtype,b.xtype
--order by 类型,匹配情况,对象名称')
【执行结果】
二、数据表结构比较
【原理】利用系统表&sysobjects"、"sysindexes"、"sysindexkeys"、&syscomments&、"sysclumns"、"systypes"、"extended_properties"，将数据库中的表结构进行对比。（涉及到系统表比较多。就不一一介绍。直接上代码。）
/*--比较两个数据库的表结构差异--*/
/*--调用示例
exec p_comparestructure 'DBNAME1','DBNAME2'
exec p_comparestructure 'DBNAME2','DBNAME3'
create proc p_comparestructure
@dbname1 varchar(250),--要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)
create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)
--得到数据库1的结构
exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and parent_obj=a.id and name in (
SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
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=''U'' and
d.name &&''dtproperties''
left join '+@dbname1+'..syscomments e on a.cdefault=e.id
left join sys.extended_properties g
a.ID=g.major_id AND a.COLID=g.minor_id
order by a.id,a.colorder')
--得到数据库2的结构
exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and parent_obj=a.id and name in (
SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
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=''U'' and
d.name &&''dtproperties''
left join '+@dbname2+'..syscomments e on a.cdefault=e.id
left join sys.extended_properties g
a.ID=g.major_id AND a.COLID=g.minor_id
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.标识 &&b.标识 then '标识不同'
when a.主键 &&b.主键 then '主键设置不同'
when a.类型 &&b.类型 then '字段类型不同'
when a.占用字节数 &&b.占用字节数 then '占用字节数'
when a.长度 &&b.长度 then '长度不同'
when a.小数位数 &&b.小数位数 then '小数位数不同'
when a.允许空 &&b.允许空 then '是否允许空不同'
when a.默认值 &&b.默认值 then '默认值不同'
when a.字段说明 &&b.字段说明 then '字段说明不同'
else '' end,
from #tb1 a
full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识 &&b.标识 or a.主键 &&b.主键 or a.类型 &&b.类型
or a.占用字节数 &&b.占用字节数 or a.长度 &&b.长度 or a.小数位数 &&b.小数位数
or a.允许空 &&b.允许空 or a.默认值 &&b.默认值 or a.字段说明 &&b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
【执行结果】
&ps：以上SQL执行请采用系统管理员（sysadmin）角色账号。其他角色我没有试过，有时间可以尝试一下。当我采用只映射了库&owner"权限的账号测试时，报如下错误：拒绝了对对象 'p_compdb' (数据库 'master'，架构 'dbo')的 EXECUTE 权限。
本文写作时，采用的是SQL SERVER 2005版本,不保证其他版本也能正常运行。如其他版本有问题，可以留言，共同学习探讨。调试环境明细如下：
Microsoft SQL Server Management Studio 9.00.1399.00Microsoft Analysis Services 客户端工具
9.00Microsoft 数据访问组件 (MDAC)
6.1. (win7_rtm.5)Microsoft MSXML
3.0 4.0 6.0&Microsoft Internet Explorer
8.0.Microsoft .NET Framework
2.0.操作系统 & 6.1.7600
阅读(...) 评论()博客访问： 9648
博文数量： 27
注册时间：
鏆傛棤浠嬬粛
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： 数据库开发技术
/*我的方法可以比较两个数据库的所有用户表结构.对于视图,存储过程,函数,个人觉得没有必要比较,只需要根据最新的数据库全部重建就行了.---------------------------------------------------------------------------------------------简单的说一下实现原理,就是从系统表中获取数据库中所有用户表的结构,将它们分别存到一个临时表中然后比较这两个表的内容,用全连接,将两个表中相同的数据全部取消不显示(就是最后处理的时候加的那个条件)对于两个表不相同的部分,再比较是那部分不相同,从而得到比较信息.*//*--比较两个数据库的表结构差异 --邹建 2003.9--*//*--调用示例
exec p_comparestructure 'tb1','tb2'--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_comparestructure]GOcreate proc p_comparestructure@dbname1 varchar(250), --要比较的数据库名1@dbname2 varchar(250) --要比较的数据库名2ascreate table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250), 占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250), 占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))--得到数据库1的结构exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid, 标识=case when a.status=0x80 then 1 else 0 end, 主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end, 类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable, 默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')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=''U'' and
d.name''dtproperties'' left join '+@dbname1+'..syscomments e on a.cdefault=e.id left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')--得到数据库2的结构exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid, 标识=case when a.status=0x80 then 1 else 0 end, 主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end, 类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable, 默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')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=''U'' and
d.name''dtproperties'' left join '+@dbname2+'..syscomments e on a.cdefault=e.id left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')--and not exists(select 1 from #tb2 where 表名2=a.表名1)select 比较结果=case when a.表名1 is null and b.序号=1 then N'库1缺少表：'+b.表名2
when b.表名2 is null and a.序号=1 then N'库2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then N'库1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then N'库2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.标识b.标识 then N'标识不同'
when a.主键b.主键 then N'主键设置不同'
when a.类型b.类型 then N'字段类型不同'
when a.占用字节数b.占用字节数 then N'占用字节数'
when a.长度b.长度 then '长度不同'
when a.小数位数b.小数位数 then N'小数位数不同'
when a.允许空b.允许空 then N'是否允许空不同'
when a.默认值b.默认值 then N'默认值不同'
when a.字段说明b.字段说明 then N'字段说明不同' else '' end, * from #tb1 a full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识b.标识 or a.主键b.主键 or a.类型b.类型 or a.占用字节数b.占用字节数 or a.长度b.长度 or a.小数位数b.小数位数 or a.允许空b.允许空 or a.默认值b.默认值 or a.字段说明b.字段说明order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)go[@more@]
阅读(1520) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。博客访问： 24618
博文数量： 51
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Linux
应用系统中一个表的应用已经上线，后期对代码进行重构，保证生成的数据结果一致。设原表为T1（A INT， B INT），新表为T2（A INT， B INT）。现比较两表中的数据是否一致，现需要获取以下统计信息：表T1的记录数（T1）；表T2的记录数（T2）；表T1与表T2同时存在的数据（T1^T2）；表T1存在而T2不存在的记录数（T1-T2）；表T2存在而T1不存在的记录数（T2-T1）；
1：若列a是主键，现只考虑字段a的数据匹配情况SELECT COUNT(T1.A) "T1",&&&&&& COUNT(T2.A) "T2",&&&&&& COUNT(CHAR(T1.A)||CHAR(T2.A)) "T1^T2",&&&&&& COUNT(1) - COUNT(T2.A) "T1-T2",&&&&&& COUNT(1) - COUNT(T1.A) "T2-T1"& FROM T1 FULL OUTER JOIN T2 &&& ON T1.A = T2.A;该查询可快速判断数据的匹配情况，若主键匹配完好后，其它字段可通过下列语句检验：SELECT * & FROM T1 FULLER OUTER JOIN T2 &&& ON T1.A = T2.A& WHERE T1.B
T2.B;2：若列a不是主键，字段a的数据匹配情况会有些变化。&& 此时数据匹配不仅值相同，而且相同值个数也相同。SELECT COUNT(T1.A) "T1",&&&&&& COUNT(T2.A) "T2",&&&&&& COUNT(CHAR(T1.A)||CHAR(T2.A)) "T1^T2",&&&&&& COUNT(1) - COUNT(T2.A) "T1-T2",&&&&&& COUNT(1) - COUNT(T1.A) "T2-T1"& FROM ( SELECT T1.*,ROW_NUMBER() OVER(PARTITION BY T1.A) NUM FROM T1 ) T1&&&&&&& FULL OUTER JOIN &&&&&& ( SELECT T2.*,ROW_NUMBER() OVER(PARTITION BY T2.A) NUM FROM T2 ) T2 &&& ON T1.A = T2.A AND T1.NUM = T2.NUM;注：当列为NULL时，count（column）聚集函数不进行计数。
阅读(437) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。请教怎么创建一个View包含两个不同结构的表的数据 - Sql Server当前位置:& &&&请教怎么创建一个View包含两个不同结构的表的数据请教怎么创建一个View包含两个不同结构的表的数据&&网友分享于：&&浏览：17次请问如何创建一个View包含两个不同结构的表的数据？表A
& FBeginTime &
& FEndTime &
& FPrice &
& FChecker
& 200.00 &
& FEndTime &
& FTtlPrice &
& FOperationer
创建View包含
FRecod（自增长） &
& FEndTime &
& FPrice &
& FChecker
& 200.00 &
该视图怎么写？谢谢 ------解决方案--------------------
Create View V_TEST
FBeginTime As FEndTime,
FTtlPrice,
FOperationer
------解决方案--------------------
select count(1) from
select FEndTime ,FPrice ,FChecker
select FEndTime, FTtlPrice,FOperationer
) tt where t.FEndTime& =tt.FEndTime
) as FRecod ,FEndTime,FPrice,FChecker
select FEndTime ,FPrice ,FChecker
select FEndTime, FTtlPrice,FOperationer
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有}