比较同一数据库不同版本间数据表之间差异

我们的项目在开发后期、或给用户升级系统时经常会碰到这样的问题：开发人员在修改数据库字段长度、更改数据库字段类型、添加主键等更改时 技术人员并没有把对数据库的操作及时、全面的记录下来。这样导致的后果是 实施人员在更新完系统后遗漏了对数据库某些更改，使系统报错。

以下脚本是自动比较该数据库两个版本之间存在的差异，并显示出来，该脚本适用与SQLSERVER 数据库。

例：比较当前库与服务器 192.168.1.1上库 aeronavy之间差异

if   exists(select * from master..sysservers where   srvname='192.168.1.1')

              begin

              EXEC sp_dropserver '192.168.1.1', 'droplogins'

              end

              --建立连接服务器

              EXEC sp_addlinkedserver

              --要创建的链接服务器名称

              --这里就用数据源作名称

              '192.168.1.1',

              'SQL Server'

      

      

              --创建链接服务器上远程登录之间的映射

              EXEC sp_addlinkedsrvlogin

              '192.168.1.1',

              'false',

              NULL,

              --远程服务器的登陆用户名

              'sa',

              --远程服务器的登陆密码

              'sa'

 

 

 

-------

--查询出俩库之间不同的表

 

select '新加表' as 操作, t.*

from

(select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

 )as t

 

where t.表名 not in (select q.name from [192.168.1.1].aeronavy.sys.all_objects as q where type='U')

order by t.表名,t.column_id asc

 

 

-----------------

--新库中删除的表

 

select '减少表' as 操作,q.name from [192.168.1.1].aeronavy.sys.all_objects as q

where q.name not in (select name from sys.all_objects where type='U')

and q.type='U'

 

-------------------------------------------------

 

--新库中删除的列

select '减少列' as 操作,q.*

from

(select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

 )as q

 

where q.表名 in (select t.表名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b

where a.object_id=b.object_id and b.type='U') as t)

and q.列名 not in (

select t.列名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b

where a.object_id=b.object_id and b.type='U') as t)

order by q.表名,q.column_id

 

---------------

-----查询出表名相同，列不同的字段

select '增加列' as 操作,t.*

from

(select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

 )as t

 

where t.表名 in (select q.name from [192.168.1.1].aeronavy.sys.all_objects as q)

and t.列名 not in (

select a.name as 列名

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

and b.name=t.表名)

order by t.表名,t.column_id

 

 

 

 

--------------------------------------------------------------------

 

------列修改长度或类型

 

select '修改列' as 操作,t.*

from

(select b.name as 表名,a.name as 列名,a.column_id,a.system_type_id,a.max_length,a.precision,a.scale,a.collation_name

from sys.all_columns as a,sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

 )as t,

(select b.name as 表名,a.name as 列名,a.column_id,a.system_type_id,a.max_length,a.precision,a.scale,a.collation_name

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b

where a.object_id=b.object_id and b.type='U'

 )as q

 

where q.表名=t.表名

and q.列名 = t.列名

 

and (q.system_type_id!=t.system_type_id or q.max_length!=t.max_length or q.precision!=t.precision or q.scale!=t.scale)

 

order by t.表名,t.column_id

 

 

 

--------------------------------------------------------------------------------------------

-----查询出 修改的主键

 

 

 

 

 

 

 

-------查看添加的主键.

 

select '增加主键' as 操作,t.* from

(select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b,sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as t

 

where t.表名 in(select q.表名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b,[192.168.1.1].aeronavy.sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as q)

and t.列名 not in (select q.列名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b,[192.168.1.1].aeronavy.sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as q)

 

------------------------------

--查看去掉的主键

select '删除主键' as 操作,q.* from

(select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from [192.168.1.1].aeronavy.sys.all_columns as a,[192.168.1.1].aeronavy.sys.all_objects as b,[192.168.1.1].aeronavy.sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as q

 

where q.表名 in (select t.表名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b,sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as t)

and q.列名 not in

(select t.列名 from (select b.name as 表名,a.name as 列名,a.column_id,a.max_length,a.system_type_id,a.collation_name

from sys.all_columns as a,sys.all_objects as b,sys.index_columns as i

where a.object_id=b.object_id and b.type='U'

and i.object_id=a.object_id and i.column_id=a.column_id) as t)

 

 

备注：以上为完整脚本 可执行，上述脚本在编写时只考虑了功能的实现，并未考虑查询效率。