sql2005 如何比较新旧两个数据库表结构差异
之前的一个数据库，现在进行了更新，添加了一些表，一些字段。
现在想知道是否能通过写t-sql进行比较两个数据表，罗列出哪些是更改或添加的？
[解决办法]
SQL复制表结构的通用存储过程 -- Transfer对象的重要属性-- 1. 属性 属性名 类型 描述--------------------------------- ------------------- --------------------CopyAllDefaults Boolean 所有默认值CopyAllObjects Boolean 所有对象CopyAllRules Boolean 所有规则CopyAllStoredProcedures Boolean 所有存储过程CopyAllTables Boolean 所有表CopyAllTriggers Boolean 所有触发器CopyAllUserDefinedDatatypes Boolean 所有用户自定义类型CopyAllViews Boolean 所有视图CopyData Boolean 所有数据DestDatabase String 目标对象数据库DestLogin String 目标数据库登陆用户名DestPassword String 目标数据库登陆密码DestServer String 目标服务器DestUseTrustedConnection Boolean 用户信任连接DropDestObjectsFirst Boolean 是否先删除目标对象IncludeDependencies Boolean 是否包含依靠对象ScriptType Boolean 脚本类型 -- 2. 重要方法: 方法名称 功能描述--------------------------- --------------------------AddObject 增加对象AddObjectByName 通过对象名称增加对象 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[P_CopyDB]GO/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移 存储过程实现源数据库到目标数据库的对象和数据的复制 要求源数据库和目标数据库在同一服务器 如果是要实现不同服务器之间的复制，则需要增加验证信息--邹建 2005.07(引用请保留此信息)--*//*--调用示例 CREATE DATABASE test EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test' DROP DATABASE test--*/CREATE PROCEDURE P_CopyDB @Des_DB sysname, --目标数据库@Obj_Type nvarchar(4000)=N'',--复制的对象类型，可以是下列字符串列表： -- O 所有对象，D 默认值，R 规则，P 存储过程 -- T 表，TR 触发器，DT 用户定义数据类型 -- V 视图，DATA 数据，DEL 删除目标对象@Source_DB sysname=N'', --源数据库@ServerName sysname=N'', --服务器名@UserName sysname=N'', --用户名，不指定则表示使用 Windows 身份登录@pwd sysname=N'' --密码 ASSET NOCOUNT ONDECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int, @err int,@src varchar(255), @desc varchar(255)IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAMEIF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME() --创建sqldmo对象·EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUTIF @err<>0 GOTO lb_Err--连接服务器IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录BEGIN EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1 IF @err<>0 GOTO lb_Err EXEC @err=sp_oamethod @srvid,'connect',NULL,@servernameENDELSE EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwdIF @err<>0 GOTO lb_Err--获取数据库集EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUTIF @err<>0 GOTO lb_Err--选择源数据库 EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DBIF @err<>0 GOTO lb_Err--选择目标数据库 EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DBIF @err<>0 GOTO lb_Err--设置复制的对象EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUTIF @err<>0 GOTO lb_Err--设置目标服务器信息EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerNameIF @err<>0 GOTO lb_Err --设置连接用户IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录BEGIN EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1 IF @err<>0 GOTO lb_ErrENDELSEBEGIN EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName IF @err<>0 GOTO lb_Err EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd IF @err<>0 GOTO lb_ErrEND --设置复制对象信息EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DBIF @err<>0 GOTO lb_Err DECLARE tb CURSOR FAST_FORWARD LOCALFORSELECT Name FROM( SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst')A WHERE CHARINDEX(KeyWord, CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0OPEN tbFETCH tb INTO @srcWHILE @@FETCH_STATUS=0BEGIN EXEC @err=sp_oasetproperty @TransferID,@src,1 IF @err<>0 GOTO lb_Err FETCH tb INTO @srcENDCLOSE tbDEALLOCATE tb--复制对象EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferIDIF @err<>0 GOTO lb_Err--结束SET @err=0GOTO lb_Exit--错误处理lb_Err: EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc) RETURN -1lb_Exit: EXEC sp_OADestroy @Dbid EXEC sp_OADestroy @srvid EXEC sp_OADestroy @TransferID RETURN @errGO
[解决办法]
SQL复制表结构的通用存储过程 -- Transfer对象的重要属性-- 1. 属性 属性名 类型 描述--------------------------------- ------------------- --------------------CopyAllDefaults Boolean 所有默认值CopyAllObjects Boolean 所有对象CopyAllRules Boolean 所有规则CopyAllStoredProcedures Boolean 所有存储过程CopyAllTables Boolean 所有表CopyAllTriggers Boolean 所有触发器CopyAllUserDefinedDatatypes Boolean 所有用户自定义类型CopyAllViews Boolean 所有视图CopyData Boolean 所有数据DestDatabase String 目标对象数据库DestLogin String 目标数据库登陆用户名DestPassword String 目标数据库登陆密码DestServer String 目标服务器DestUseTrustedConnection Boolean 用户信任连接DropDestObjectsFirst Boolean 是否先删除目标对象IncludeDependencies Boolean 是否包含依靠对象ScriptType Boolean 脚本类型 -- 2. 重要方法: 方法名称 功能描述--------------------------- --------------------------AddObject 增加对象AddObjectByName 通过对象名称增加对象 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[P_CopyDB]GO/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移 存储过程实现源数据库到目标数据库的对象和数据的复制 要求源数据库和目标数据库在同一服务器 如果是要实现不同服务器之间的复制，则需要增加验证信息--邹建 2005.07(引用请保留此信息)--*//*--调用示例 CREATE DATABASE test EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test' DROP DATABASE test--*/CREATE PROCEDURE P_CopyDB @Des_DB sysname, --目标数据库@Obj_Type nvarchar(4000)=N'',--复制的对象类型，可以是下列字符串列表： -- O 所有对象，D 默认值，R 规则，P 存储过程 -- T 表，TR 触发器，DT 用户定义数据类型 -- V 视图，DATA 数据，DEL 删除目标对象@Source_DB sysname=N'', --源数据库@ServerName sysname=N'', --服务器名@UserName sysname=N'', --用户名，不指定则表示使用 Windows 身份登录@pwd sysname=N'' --密码 ASSET NOCOUNT ONDECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int, @err int,@src varchar(255), @desc varchar(255)IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAMEIF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME() --创建sqldmo对象·EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUTIF @err<>0 GOTO lb_Err--连接服务器IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录BEGIN EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1 IF @err<>0 GOTO lb_Err EXEC @err=sp_oamethod @srvid,'connect',NULL,@servernameENDELSE EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwdIF @err<>0 GOTO lb_Err--获取数据库集EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUTIF @err<>0 GOTO lb_Err--选择源数据库 EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DBIF @err<>0 GOTO lb_Err--选择目标数据库 EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DBIF @err<>0 GOTO lb_Err--设置复制的对象EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUTIF @err<>0 GOTO lb_Err--设置目标服务器信息EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerNameIF @err<>0 GOTO lb_Err --设置连接用户IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录BEGIN EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1 IF @err<>0 GOTO lb_ErrENDELSEBEGIN EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName IF @err<>0 GOTO lb_Err EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd IF @err<>0 GOTO lb_ErrEND --设置复制对象信息EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DBIF @err<>0 GOTO lb_Err DECLARE tb CURSOR FAST_FORWARD LOCALFORSELECT Name FROM( SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst')A WHERE CHARINDEX(KeyWord, CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0OPEN tbFETCH tb INTO @srcWHILE @@FETCH_STATUS=0BEGIN EXEC @err=sp_oasetproperty @TransferID,@src,1 IF @err<>0 GOTO lb_Err FETCH tb INTO @srcENDCLOSE tbDEALLOCATE tb--复制对象EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferIDIF @err<>0 GOTO lb_Err--结束SET @err=0GOTO lb_Exit--错误处理lb_Err: EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc) RETURN -1lb_Exit: EXEC sp_OADestroy @Dbid EXEC sp_OADestroy @srvid EXEC sp_OADestroy @TransferID RETURN @errGO
[解决办法]
*--数据结构比较 比较两个数据库中的视图/存储过程的结构(结构比较,不是功能比较) --邹建 2004.07(引用请保留此信息)--*/ /*--调用示例 --调用 exec p_compdb 'pubs','northwind' --*/ create proc p_compdb @db1 sysname, --第一个库 @db2 sysname --第二个库 as exec(' 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) from( 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 类型,匹配情况,对象名称') go 表结构比较： ------------------------------------------------------- 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
[解决办法]
用sysobjects和syscolumns表来查询
http://www.dbank.com/download?f=c0kf8t9hg3&i=2359&h=1302155793&v=583187fa