如何比较两个数据库的差异
答案:2 悬赏:0 手机版
解决时间 2021-11-07 11:43
- 提问者网友:孤凫
- 2021-11-06 18:30
如何比较两个数据库的差异
最佳答案
- 五星知识达人网友:孤独入客枕
- 2021-11-06 19:01
构建两个临时表,将两个数据库结构信息导入。
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
比较两个数据库中表和字段的差异
-- 比较两个数据库中表的差异
-- u表,p存储过程,v视图
-- INTFSIMSNEW新库,INTFSIMS旧库
SELECt NTABLE = A.NAME, OTABLE = B.NAME
FROM INTFSIMSNEW..SYSOBJECTS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.NAME = B.NAME
WHERe ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
UNIOn ALL
SELECt NTABLE = B.NAME, OTABLE = A.NAME
FROM INTFSIMS..SYSOBJECTS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.NAME = B.NAME
WHERe ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
ORDER BY 1, 2
-- 比较两个数据库中每个表字段的差异
SELECt
表名A = CASE WHEN ISNULL(A.TABLENAME, '') <> '' THEN A.TABLENAME ELSE B.TABLENAME END,
字段名A = A.FIELDNAME,
字段名B = B.FIELDNAME,
顺序= A.FIELDSNO,
说明= CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN '类型: ' + A.FIELDTYPE + '-->' + B.FIELDTYPE
WHEN A.FIELDSNO <> B.FIELDSNO THEN '顺序: ' + str(A.FIELDSNO) + '-->' + str(B.FIELDSNO)
WHEN A.LENGTH <> B.LENGTH THEN '长度: ' + str(A.LENGTH) + '-->' + str(B.LENGTH)
WHEN A.LENSEC <> B.LENSEC THEN '小数位: ' + str(A.LENSEC) + '-->' + str(B.LENSEC)
WHEN A.ALLOWNULL <> B.ALLOWNULL THEN '允许空值: ' + str(A.ALLOWNULL) + '-->' + str(B.ALLOWNULL)
END
FROM (SELECt
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMSNEW..SYSCOLUMNS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMSNEW..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERe B.XTYPE = 'U') A
FULL JOIN (SELECt
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMS..SYSCOLUMNS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMS..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERe B.XTYPE = 'U') B
ON A.TABLENAME = B.TABLENAME
AND A.FIELDNAME = B.FIELDNAME
WHERe ISNULL(A.TABLENAME, '') = ''
OR ISNULL(B.TABLENAME, '') = ''
OR A.FIELDTYPE <> B.FIELDTYPE
OR A.FIELDSNO <> B.FIELDSNO
OR A.LENGTH <> B.LENGTH
OR A.LENSEC <> B.LENSEC
OR A.ALLOWNULL <> B.ALLOWNULL
ORDER by 1, 4
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
比较两个数据库中表和字段的差异
-- 比较两个数据库中表的差异
-- u表,p存储过程,v视图
-- INTFSIMSNEW新库,INTFSIMS旧库
SELECt NTABLE = A.NAME, OTABLE = B.NAME
FROM INTFSIMSNEW..SYSOBJECTS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.NAME = B.NAME
WHERe ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
UNIOn ALL
SELECt NTABLE = B.NAME, OTABLE = A.NAME
FROM INTFSIMS..SYSOBJECTS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.NAME = B.NAME
WHERe ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
ORDER BY 1, 2
-- 比较两个数据库中每个表字段的差异
SELECt
表名A = CASE WHEN ISNULL(A.TABLENAME, '') <> '' THEN A.TABLENAME ELSE B.TABLENAME END,
字段名A = A.FIELDNAME,
字段名B = B.FIELDNAME,
顺序= A.FIELDSNO,
说明= CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN '类型: ' + A.FIELDTYPE + '-->' + B.FIELDTYPE
WHEN A.FIELDSNO <> B.FIELDSNO THEN '顺序: ' + str(A.FIELDSNO) + '-->' + str(B.FIELDSNO)
WHEN A.LENGTH <> B.LENGTH THEN '长度: ' + str(A.LENGTH) + '-->' + str(B.LENGTH)
WHEN A.LENSEC <> B.LENSEC THEN '小数位: ' + str(A.LENSEC) + '-->' + str(B.LENSEC)
WHEN A.ALLOWNULL <> B.ALLOWNULL THEN '允许空值: ' + str(A.ALLOWNULL) + '-->' + str(B.ALLOWNULL)
END
FROM (SELECt
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMSNEW..SYSCOLUMNS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMSNEW..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERe B.XTYPE = 'U') A
FULL JOIN (SELECt
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMS..SYSCOLUMNS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMS..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERe B.XTYPE = 'U') B
ON A.TABLENAME = B.TABLENAME
AND A.FIELDNAME = B.FIELDNAME
WHERe ISNULL(A.TABLENAME, '') = ''
OR ISNULL(B.TABLENAME, '') = ''
OR A.FIELDTYPE <> B.FIELDTYPE
OR A.FIELDSNO <> B.FIELDSNO
OR A.LENGTH <> B.LENGTH
OR A.LENSEC <> B.LENSEC
OR A.ALLOWNULL <> B.ALLOWNULL
ORDER by 1, 4
全部回答
- 1楼网友:春色三分
- 2021-11-06 20:14
什么数据库啊
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯