Sql 2005 中比较两个数据库差异
构建两个临时表,将两个数据库结构信息导入。
create Table #t1
(
ID Int Identity(1,1) Not Null Primary Key,
tablename nvarchar(50) NULL,
columnName nvarchar(50) NULL,
columnIndex int null,
columnType nvarchar(50) NULL
)
use 数据库1
insert into #t1
create Table #t2
(
ID Int Identity(1,1) Not Null Primary Key,
tablename nvarchar(50) NULL,
columnName nvarchar(50) NULL,
columnIndex int null,
columnType nvarchar(50) NULL
)
//开始比较
use 数据库2
insert into #t2
SELECT
SO.name as '表名',
SC.name as '表列名',
SC.colid as '索引',
ST.name as '类型'
FROM
sysobjects SO, -- 对象表
syscolumns SC, -- 列名表
systypes ST -- 数据类型表
WHERE
SO.id = SC.id
AND SO.xtype = 'U' -- 类型U表示表,V表示视图
AND SO.status >= 0 --加一个条件:SO.status >= 0,否则会将系统的临时表显示出来
AND SC.xtype = ST.xusertype
ORDER BY
SO.name, SC.colorder
go
//查询出 在t1 里有, t2 里没有的字段,查询列出来。
select * from
(
select tablename,columnName,columnType from #t1 where tablename like '%EMS_%'
EXCEPT
select tablename,columnName,columnType from #t2 where tablename like '%EMS_%'
) as c
order by tablename