21
2015
05

对两个相同MSSql数据库表、字段描述进行对比,找出表字段之间的差异

在两个数据库进行同步或者对比的时候,需要对比两个数据库之间的差异,使用Sql语句进行对比分析,具体操作如下:

1、创建两个表(BackUp_TableInfo、BackUp_TableInfo1)来进行存储表结构信息,即表的所有字段信息,这里需要在两个数据库中都同时创建,也可以根据需要两个数据库中分别创建,Sql语句:

/*创建一个表来存在当前数据库所有表及字段、描述等信息,用于对比两个数据库是否一致*/
--创建表存储数据库结构信息
if exists (select 1 from  sysobjects where  id = object_id('BackUp_TableInfo') and type = 'U') drop table BackUp_TableInfo
CREATE TABLE [dbo].[BackUp_TableInfo](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NULL,
[FieldName] [varchar](50) NULL,
[LogoName] [varchar](50) NULL,
[KeyName] [varchar](50) NULL,
[TypeName] [varchar](50) NULL,
[LengthName] [int] NULL,
[ISNullName] [varchar](50) NULL,
[DefaultName] [varchar](50) NULL,
[DescriptionName] [nvarchar](500) NULL
) ON [PRIMARY]
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'TableName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'FieldName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否是自动标识列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'LogoName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否是主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'KeyName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'TypeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段类型长度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'LengthName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为空' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'ISNullName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'默认值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'DefaultName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo', @level2type=N'COLUMN',@level2name=N'DescriptionName'
GO
if exists (select 1 from  sysobjects where  id = object_id('BackUp_TableInfo1') and type = 'U') drop table BackUp_TableInfo1
CREATE TABLE [dbo].[BackUp_TableInfo1](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NULL,
[FieldName] [varchar](50) NULL,
[LogoName] [varchar](50) NULL,
[KeyName] [varchar](50) NULL,
[TypeName] [varchar](50) NULL,
[LengthName] [int] NULL,
[ISNullName] [varchar](50) NULL,
[DefaultName] [varchar](50) NULL,
[DescriptionName] [nvarchar](500) NULL
) ON [PRIMARY] 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'TableName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'FieldName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否是自动标识列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'LogoName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否是主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'KeyName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'TypeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段类型长度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'LengthName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为空' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'ISNullName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'默认值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'DefaultName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BackUp_TableInfo1', @level2type=N'COLUMN',@level2name=N'DescriptionName'
GO

--创建表存储数据库结构信息

2、将数据库中所有字段信息存储到第一步中创建的两个表,这里也是需要在两个表中同时创建,Sql语句:

--导入当前数据库表及字段、描述
INSERT INTO BackUp_TableInfo(TableName,FieldName,LogoName,KeyName,TypeName,LengthName,ISNullName,DefaultName,DescriptionName) 
SELECT CONVERT(VARCHAR(50), TableName) TableName,CONVERT(VARCHAR(50), FieldName) FieldName,
CONVERT(VARCHAR(50), LogoName) LogoName,CONVERT(VARCHAR(50), KeyName) KeyName,
CONVERT(VARCHAR(50), TypeName) TypeName,LengthName,CONVERT(VARCHAR(50), ISNullName) 
ISNullName,CONVERT(NVARCHAR(50), DefaultName) DefaultName,
CONVERT(VARCHAR(500), DescriptionName) DescriptionName from 
(SELECT 
TableName = d.name,
FieldName = a.name,
LogoName = CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '是'ELSE '否' END,
KeyName = CASE WHEN exists(SELECT 1 FROM sysobjects WHERE xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) THEN '是' ELSE '否' END,
TypeName = b.name,
LengthName = a.length,
ISNullName = CASE WHEN a.isnullable=1 THEN '是' ELSE '否' END,
DefaultName = ISNULL(e.text,''),
DescriptionName = ISNULL(g.value,'') 
FROM syscolumns a left join systypes b 
on a.xusertype=b.xusertype inner join sysobjects d 
on a.id=d.id  and (d.xtype='U' or d.xtype='V') and  d.name<>'dtproperties' left join syscomments e 
on a.cdefault=e.id left join sys.extended_properties g 
on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f 
on d.id=f.major_id and f.minor_id=0 /*WHERE d.name<>'TravelAgency'*/) a
--导入当前数据库表及字段、描述

3、在其中一个数据库的服务器上把另一个数据库表字段导入到BackUp_TableInfo1中,可以根据需要选择在那个数据库中进行导入表字段信息,Sql语句:

/*不同服务器数据库之间的数据操作*/
--创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '数据库连接IP地址' 
exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '数据库用户名', '数据库密码' 
--查询创建链接后的某个表
INSERT INTO BackUp_TableInfo1(TableName,FieldName,LogoName,KeyName,TypeName,LengthName,ISNullName,DefaultName,DescriptionName) 
SELECT TableName,FieldName,LogoName,KeyName,TypeName,LengthName,ISNullName,DefaultName,DescriptionName FROM ITSV.TourismManage_New.dbo.BackUp_TableInfo 
--以后不再使用时删除链接服务器 
exec sp_dropserver  'ITSV ', 'droplogins '
/*不同服务器数据库之间的数据操作*/

4、写sql语句进行表字段对比,Sql语句:

/*查询对应表是否一致*/
select * from BackUp_TableInfo left join BackUp_TableInfo1 
on BackUp_TableInfo.TableName=BackUp_TableInfo1.TableName and 
BackUp_TableInfo.FieldName=BackUp_TableInfo1.FieldName where BackUp_TableInfo.TableName<>'BackUp_TableInfo' and 
BackUp_TableInfo.TableName<>'BackUp_TableInfo1' and (BackUp_TableInfo1.ID is null or BackUp_TableInfo.LogoName<>BackUp_TableInfo1.LogoName or 
BackUp_TableInfo.KeyName<>BackUp_TableInfo1.KeyName or BackUp_TableInfo.TypeName<>BackUp_TableInfo1.TypeName or
BackUp_TableInfo.LengthName<>BackUp_TableInfo1.LengthName or BackUp_TableInfo.ISNullName<>BackUp_TableInfo1.ISNullName or
BackUp_TableInfo.DefaultName<>BackUp_TableInfo1.DefaultName or BackUp_TableInfo.DescriptionName<>BackUp_TableInfo1.DescriptionName)
/*查询对应表是否一致*/
/*创建一个表来存在当前数据库所有表及字段、描述等信息,用于对比几个数据库是否一致*/

结束语:通过对比,可以看出两个数据库之间的表那些字段存在差异,包括表字段的描述、表字段的长度、表字段的类型等等,下图中通过对比,发现有3个表不一致,都是新建的。

« 上一篇下一篇 »

评论列表:

1.网上兼职  2015/5/21 11:54:23 回复该留言
写的不错,赞!
2.那朵花  2015/5/22 11:25:34 回复该留言
程序员同学你好!!!
3.boke123导航  2015/6/3 13:59:20 回复该留言
好久没玩过数据库字段了,用了WordPress好像都不怎么用折腾数据库

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。