问个数据库内容比较的问题

Question

表结构比较：
-------------------------------------------------------------------------------------------
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]
GO/*--比较两个数据库的表结构差异

可以比较两个数据库的结构差异--邹建 2003.9(引用请保留此信息)--*//*--调用示例

exec p_comparestructure '库1','库2'
--*/
create proc p_comparestructure
@dbname1 varchar(250), --要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
as
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 '+@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 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 '+@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 '库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.字段名)
go

Answer 1

谢谢libin_ftsafe
让我考虑下,这个思路似乎可行.我的意思是说在检验700张表格时候思路应该可以这么走,如果这两天想出思路就结帖防分.

Answer 2

自己再追加内容.
昨天被数据库的不同步给弄郁闷了，开发和测试的库不是一个，开发的这个库，存储过程经常更新。有时候甚至表结构都有些变化。
这就导致了很多问题。一时半会还不知道具体是什么问题，搞得老子很狼狈，一时性起，想写一个程序来比较两个库的不同，方便测试和实施。
今天上午,边开会边写,中午的时候通过测试.主要是两个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）
 
现在只做了比较表和视图的列属性,以及存储过程和函数.
没有实现比较表之间了约束关系.
以后加上. 

Answer 3

楼主是想看别人的程序某个操作改动了哪几个表吧,你开监控就可以看到啊,按一下操作按钮,然后看SQL语句就OK