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个表不一致，都是新建的。

Tags: MSSql  Sql语句  对比  差异  
发布:郑德才博客 | 分类:学习之路 | 评论:3 | 浏览:
« 上一篇下一篇 »

相关文章:

MSSql删除数据库中所有表sql语句  (2016-4-3 22:1:16)

MSSql查询数据库中所有表的字段及说明sql语句  (2016-4-3 21:52:18)

Sql语句实现获取汉字首字母和全拼SQLSERVER函数  (2015-5-22 13:41:45)

评论列表:

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

发表评论:

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