比较SQL Server 2000 数据库中两个库的差异

转载 2006年05月27日 16:42:00
昨天被数据库的不同步给弄郁闷了,开发和测试的库不是一个,开发的这个库,存储过程经常更新。有时候甚至表结构都有些变化。
这就导致了很多问题。一时半会还不知道具体是什么问题,搞得老子很狼狈,一时性起,想写一个程序来比较两个库的不同,方便测试和实施。
今天上午,边开会边写,中午的时候通过测试.

主要是两个SQL语句:
查询库中表和视图结构和列属性不同的SQL如下:

select A. [name] as TableName,
       B. [name] as Colname,
       B.xtype,
       B.xusertype,
       B.length,
       B.colid,
       B.cdefault,
       B.domain,
       B.number,
       B.offset,
       B.status,
       B.type,
       B.usertype,
       B.prec,
       B.scale,
       B.iscomputed,
       B.isoutparam,
       B.isnullable,
       C.COLUMN_DEFAULT,
       dbo.fnIsColumnPrimaryKey(B. [ID], B. [name]) as PKey
  from sysobjects A, syscolumns B, INFORMATION_SCHEMA .COLUMNS C
 where a. id = B. id
   and A.xtype in ('u', 'v')
   and A. Name = C.TABLE_NAME
   and B. Name = C.COLUMN_NAME
 order by A. [ID], B. [Name]

///////
每列代表的意思如下
name sysname 列名或过程参数的名称。
    id int 该列所属的表对象 ID,或与该参数关联的存储过程 ID。
    xtype tinyint systypes 中的物理存储类型。
    typestat tinyint 仅限内部使用。
    xusertype smallint 扩展的用户定义数据类型 ID。
    length smallint systypes 中的最大物理存储长度。
    xprec tinyint 仅限内部使用。
    xscale tinyint 仅限内部使用。
    colid smallint 列或参数 ID。
    xoffset smallint 仅限内部使用。
    bitpos tinyint 仅限内部使用。
    reserved tinyint 仅限内部使用。
    colstat smallint 仅限内部使用。
    cdefault int 该列的默认值 ID。
    domain int 该列的规则或 CHECK 约束 ID。
    number smallint 过程分组时(0 表示非过程项)的子过程号。
    colorder smallint 仅限内部使用。
    autoval varbinary(255) 仅限内部使用。
    offset smallint 该列所在行的偏移量;如果为负,表示可变长度行。
    status tinyint 用于描述列或参数属性的位图:
    0x08 = 列允许空值。
    0x10 = 当添加 varchar 或 varbinary 列时,ANSI 填充生效。保留 varchar 列的尾随空格,保留 varbinary 列的尾随零。
    0x40 = 参数为 OUTPUT 参数。
    0x80 = 列为标识列。
     
    type tinyint systypes 中的物理存储类型。
    usertype smallint systypes 中的用户定义数据类型 ID。
    printfmt varchar(255) 仅限内部使用。
    prec smallint 该列的精度级别。
    scale int 该列的小数位数。
    iscomputed int 表示是否已计算该列的标志:
    0 = 未计算。
    1 = 已计算。
    
    isoutparam int 表示该过程参数是否是输出参数:
    1 = 真。
    0 = 假。
 
    isnullable int 表示该列是否允许空值:
    1 = 真。
    0 = 假。
   
    COLUMN_DEFAULT 默认值
   
    PKey 主键

////////
用到了如下函数:
---------------------
CREATE    FUNCTION dbo.fnIsColumnPrimaryKey(@sTableID int, @nColumnName varchar(128))
--alter   FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
 DECLARE @nTableID int,
  @nIndexID int,
  @i int
 
 SET  @nTableID =  @sTableID--OBJECT_ID(@sTableName)
 
 SELECT  @nIndexID = indid
 FROM  sysindexes
 WHERE  id = @nTableID
  AND  indid BETWEEN 1 And 254
  AND  (status & 2048) = 2048
 
 IF @nIndexID Is Null
  RETURN 0
 
 IF @nColumnName IN
  (SELECT sc.[name]
  FROM  sysindexkeys sik
   INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
  WHERE  sik.id = @nTableID
   AND  sik.indid = @nIndexID)
  BEGIN
  RETURN 1
  END

 RETURN 0
END
---------------------
通过这个SQL语句可以查出数据库中所有表的列属性。
通过分别执行两次这个SQL语句,就可以取出两个库中列的属性比较了。
----------------------------------------------------------------
对于存储过程和函数 用下面的SQL语句:
 select * from sysobjects  where xtype in ('P','TF','IF','FN')  order by [Name]
可以查询出所有的存储过程和函数名。循环查询出来的 结果,
每个循环中 将函数或存储过程名存到一字符串SpobjectName中,然后调用 存储过程: sp_helptext 如下:
 exec sp_helptext '" +SpobjectName+"'
返回一个表 表中就是 SpobjectName 存储过程中的文本类容 。存储过程中一行文本对应表中一行。
把所有的SP和函数把用 exec sp_helptext 执行就得到了所有的存储过程和函数的代码。
将得到的结果放一个表中再比较异同就是了。

参考:SQL Server 联机丛书
< 数据库中存储过程的自动化生成>(http://www.vckbase.com/document/viewdoc/?id=1111)
 
现在只做了比较表和视图的列属性,以及存储过程和函数.
没有实现比较表之间了约束关系.
以后加上.

SQL Server 比较两个数据库的视图和存储过程结构差异

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]....
  • tuzhen007
  • tuzhen007
  • 2014年02月26日 11:28
  • 1033

SQL SERVER 比较两个数据库中表和字段的差异

在开发过程中线上的数据库表字段和本地数据库表字段是存在的,也许我们在本地数据库中所增加的表字段都会有记录到SQL文件中,但当增加的表及字段名称较多时总会出现漏网之鱼,发布真是版本的时候回出现很多很多的...
  • XPY567
  • XPY567
  • 2016年11月29日 20:01
  • 3282

比较两个数据库的表结构差异

/*--比较两个数据库的表结构差异 --邹建 2003.9(引用请保留此信息)--*//*--调用示例  exec p_comparestructure xzkh_model,xzkh_new--*/...
  • zjcxc
  • zjcxc
  • 2004年01月04日 10:22
  • 9615

比较两个SQLSERVER的数据结构的不同

 Option Explicit------------------------------------------------------------比较两个SQLSERVER的数据结构的不同  作...
  • gdcyh
  • gdcyh
  • 2007年08月11日 09:40
  • 1283

【SHELL】如何对比两个数据库差异?

今天一个同事碰到一个问题:要比较两个数据库文件的差异,数据量太大,一个一个看是不现实的,有什么办法更快一点呢?中午睡觉的时候,作了一些尝试,终于想到一个办法。具体的办法如下:一、先安装sqlite3,...
  • u013819292
  • u013819292
  • 2016年06月07日 15:35
  • 1388

如何比较两个Oracle数据库之间对象的不同

 关于这点,PL/SQL 已经帮我们做了菜单: Tools -> Compare User Objects进入后选定需要比较的对象,再指定比较目标数据库,即可完成比较。...
  • likelynn
  • likelynn
  • 2008年02月24日 15:51
  • 2436

SQLSERVER(一) 快速比较两个张,相同和不同的数据!

--相同数据  select  *  from a  intersect  select  *  from b 通过上面的语句,可以返回两张表相同的数据。   --不同数...
  • ufocode
  • ufocode
  • 2015年01月19日 10:06
  • 613

sql server 数据库差异还原

一、差异还原 差异备份  ---------------------------------------------  (1)基于完全备份。  (2)备份自最近一次完全备份以来的所有数据库改变。  (...
  • nic7968
  • nic7968
  • 2016年05月11日 12:28
  • 574

SQL SERVER tablediff比较表是否一致工具

1. tablediff 是什么? tablediff 实用工具用于比较两个非收敛的表中的数据,它对于排除复制拓扑中的非收敛故障非常有用。  2. tablediff 用哪些用法? 1) . 在充当复...
  • u011680118
  • u011680118
  • 2016年08月26日 12:49
  • 605

查询两个表的字段属性差异的sql语句

我们经常在测试库表中 插入数据没问题,但是在正式库同一张表中插入数据就会有问题 这种情况常常是因为 某一张表 修改了 字段属性之后 没有对应的修改 另一张表的属性  那我们怎么能找出那些有区别...
  • q383965374
  • q383965374
  • 2014年12月02日 09:32
  • 2038
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:比较SQL Server 2000 数据库中两个库的差异
举报原因:
原因补充:

(最多只允许输入30个字)