两个数据库对比

yz727272 2011-06-01 10:03:19

sql2005

如何用一条SQL语句，检查出两个数据库哪些结构不同，

意思，检查出不同的表，和字段

...全文

102 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

songguangqin 2011-06-01

打赏
举报

[Quote=引用 1 楼 maco_wang 的回复:]
http://blog.csdn.net/zjcxc/archive/2004/01/04/20088.aspx
看邹老大的博客
[/Quote]
向邹老大看齐~~

mingpei0703 2011-06-01

打赏
举报

[Quote=引用 1 楼 maco_wang 的回复:]
http://blog.csdn.net/zjcxc/archive/2004/01/04/20088.aspx
看邹老大的博客
[/Quote]
+1
这个是较完整的

叶子 2011-06-01

打赏
举报

http://blog.csdn.net/zjcxc/archive/2004/01/04/20088.aspx
看邹老大的博客

Shawn 2011-06-01

打赏
举报

邹老大的SQL2000版本的。2005如下：

IF OBJECT_ID(N'[dbo].[up_compareDBstructure]', 'P') IS NOT NULL

	DROP PROCEDURE [dbo].[up_compareDBstructure]

GO



CREATE PROCEDURE [dbo].[up_compareDBstructure]

	@dbname1 VARCHAR(250),

	@dbname2 VARCHAR(250)

AS

SET NOCOUNT ON



DECLARE

	@sql1 NVARCHAR(MAX),

	@sql2 NVARCHAR(MAX)



IF OBJECT_ID(N'tempdb..#tb1', 'U') IS NOT NULL

	DROP TABLE #tb1

IF OBJECT_ID(N'tempdb..#tb2', 'U') IS NOT NULL

	DROP TABLE #tb2



CREATE TABLE #tb1

(

	表名1 NVARCHAR(128),

	字段名 NVARCHAR(128),

	序号 INT,

	标识 NVARCHAR(1),

	主键 NVARCHAR(1),

	类型 NVARCHAR(128),

	占用字节数 INT,

	长度 INT,

	小数位数 INT,

	允许空 NVARCHAR(1),

	默认值 VARCHAR(MAX),

	字段说明 SQL_VARIANT

)

CREATE TABLE #tb2

(

	表名2 NVARCHAR(128),

	字段名 NVARCHAR(128),

	序号 INT,

	标识 NVARCHAR(1),

	主键 NVARCHAR(1),

	类型 NVARCHAR(128),

	占用字节数 INT,

	长度 INT,

	小数位数 INT,

	允许空 NVARCHAR(1),

	默认值 VARCHAR(MAX),

	字段说明 SQL_VARIANT

)



SET @sql1 = N'SELECT

	TableName=O.name, 

	--TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''''), 

	ColumnName=C.name, 

	Column_id=C.column_id, 

	[IDENTITY]=CASE WHEN C.is_identity=1 THEN N''√''ELSE N'''' END, 

	PrimaryKey=ISNULL(IDX.PrimaryKey,N''''), 

	--Computed=CASE WHEN C.is_computed=1 THEN N''√''ELSE N'''' END, 

	Type=T.name, 

	Length=C.max_length, 

	Precision=C.precision, 

	Scale=C.scale, 

	NullAble=CASE WHEN C.is_nullable=1 THEN N''√''ELSE N'''' END, 

	[Default]=ISNULL(D.definition,N''''), 

	ColumnDesc=ISNULL(PFD.[value],N'''') 

	--IndexName=ISNULL(IDX.IndexName,N''''), 

	--IndexSort=ISNULL(IDX.Sort,N''''), 

	--Create_Date=O.Create_Date, 

	--Modify_Date=O.Modify_date 

FROM '+@dbname1+N'.sys.columns C 

	INNER JOIN '+@dbname1+N'.sys.objects O 

		ON C.[object_id]=O.[object_id] 

			AND O.type=''U'' 

			AND O.is_ms_shipped=0 

	INNER JOIN '+@dbname1+N'.sys.types T 

		ON C.user_type_id=T.user_type_id 

	LEFT JOIN '+@dbname1+N'.sys.default_constraints D 

		ON C.[object_id]=D.parent_object_id 

			AND C.column_id=D.parent_column_id 

			AND C.default_object_id=D.[object_id] 

	LEFT JOIN '+@dbname1+N'.sys.extended_properties PFD 

		ON PFD.class=1 

			AND C.[object_id]=PFD.major_id 

			AND C.column_id=PFD.minor_id 

			-- AND PFD.name=''Caption'' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) 

	LEFT JOIN '+@dbname1+N'.sys.extended_properties PTB 

		ON PTB.class=1 

			AND PTB.minor_id=0 

			AND C.[object_id]=PTB.major_id 

			-- AND PFD.name=''Caption'' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 

	LEFT JOIN -- 索引及主键信息 

	( 

		SELECT 

			IDXC.[object_id], 

			IDXC.column_id, 

			Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,''IsDescending'') 

					WHEN 1 THEN ''DESC'' WHEN 0 THEN ''ASC'' ELSE '''' END, 

			PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N''√''ELSE N'''' END, 

			IndexName=IDX.Name 

		FROM '+@dbname1+N'.sys.indexes IDX 

			INNER JOIN '+@dbname1+N'.sys.index_columns IDXC 

				ON IDX.[object_id]=IDXC.[object_id] 

					AND IDX.index_id=IDXC.index_id 

			LEFT JOIN '+@dbname1+N'.sys.key_constraints KC 

				ON IDX.[object_id]=KC.[parent_object_id] 

					AND IDX.index_id=KC.unique_index_id 

			INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 

			( 

				SELECT [object_id], Column_id, index_id=MIN(index_id) 

				FROM '+@dbname1+N'.sys.index_columns 

				GROUP BY [object_id], Column_id 

			) IDXCUQ 

				ON IDXC.[object_id]=IDXCUQ.[object_id] 

					AND IDXC.Column_id=IDXCUQ.Column_id 

					AND IDXC.index_id=IDXCUQ.index_id 

	) IDX 

		ON C.[object_id]=IDX.[object_id] 

			AND C.column_id=IDX.column_id 

--WHERE O.name=N''abc'' -- 如果只查询指定表,加上此条件 

'



SET @sql2 = N'SELECT

	TableName=O.name, 

	--TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''''), 

	ColumnName=C.name, 

	Column_id=C.column_id, 

	[IDENTITY]=CASE WHEN C.is_identity=1 THEN N''√''ELSE N'''' END, 

	PrimaryKey=ISNULL(IDX.PrimaryKey,N''''), 

	--Computed=CASE WHEN C.is_computed=1 THEN N''√''ELSE N'''' END, 

	Type=T.name, 

	Length=C.max_length, 

	Precision=C.precision, 

	Scale=C.scale, 

	NullAble=CASE WHEN C.is_nullable=1 THEN N''√''ELSE N'''' END, 

	[Default]=ISNULL(D.definition,N''''), 

	ColumnDesc=ISNULL(PFD.[value],N'''') 

	--IndexName=ISNULL(IDX.IndexName,N''''), 

	--IndexSort=ISNULL(IDX.Sort,N''''), 

	--Create_Date=O.Create_Date, 

	--Modify_Date=O.Modify_date 

FROM '+@dbname2+N'.sys.columns C 

	INNER JOIN '+@dbname2+N'.sys.objects O 

		ON C.[object_id]=O.[object_id] 

			AND O.type=''U'' 

			AND O.is_ms_shipped=0 

	INNER JOIN '+@dbname2+N'.sys.types T 

		ON C.user_type_id=T.user_type_id 

	LEFT JOIN '+@dbname2+N'.sys.default_constraints D 

		ON C.[object_id]=D.parent_object_id 

			AND C.column_id=D.parent_column_id 

			AND C.default_object_id=D.[object_id] 

	LEFT JOIN '+@dbname2+N'.sys.extended_properties PFD 

		ON PFD.class=1 

			AND C.[object_id]=PFD.major_id 

			AND C.column_id=PFD.minor_id 

			-- AND PFD.name=''Caption'' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) 

	LEFT JOIN '+@dbname2+N'.sys.extended_properties PTB 

		ON PTB.class=1 

			AND PTB.minor_id=0 

			AND C.[object_id]=PTB.major_id 

			-- AND PFD.name=''Caption'' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 

	LEFT JOIN -- 索引及主键信息 

	( 

		SELECT 

			IDXC.[object_id], 

			IDXC.column_id, 

			Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,''IsDescending'') 

					WHEN 1 THEN ''DESC'' WHEN 0 THEN ''ASC'' ELSE '''' END, 

			PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N''√''ELSE N'''' END, 

			IndexName=IDX.Name 

		FROM '+@dbname2+N'.sys.indexes IDX 

			INNER JOIN '+@dbname2+N'.sys.index_columns IDXC 

				ON IDX.[object_id]=IDXC.[object_id] 

					AND IDX.index_id=IDXC.index_id 

			LEFT JOIN '+@dbname2+N'.sys.key_constraints KC 

				ON IDX.[object_id]=KC.[parent_object_id] 

					AND IDX.index_id=KC.unique_index_id 

			INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 

			( 

				SELECT [object_id], Column_id, index_id=MIN(index_id) 

				FROM '+@dbname2+N'.sys.index_columns 

				GROUP BY [object_id], Column_id 

			) IDXCUQ 

				ON IDXC.[object_id]=IDXCUQ.[object_id] 

					AND IDXC.Column_id=IDXCUQ.Column_id 

					AND IDXC.index_id=IDXCUQ.index_id 

	) IDX 

		ON C.[object_id]=IDX.[object_id] 

			AND C.column_id=IDX.column_id 

--WHERE O.name=N''abc'' -- 如果只查询指定表,加上此条件 

'



--GET DB STRUCTURE

exec('insert into #tb1 '+@sql1)

exec('insert into #tb2 '+@sql2)



--COMPARE

select 比较结果=case when a.表名1 is null and b.序号=1 then N'库1缺少表：'+b.表名2

  when b.表名2 is null and a.序号=1 then N'库2缺少表:'+a.表名1

  when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then N'库1 ['+b.表名2+N'] 缺少字段：'+b.字段名

  when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then N'库2 ['+a.表名1+N'] 缺少字段：'+a.字段名

  when a.标识<>b.标识 then N'标识不同'

  when a.主键<>b.主键 then N'主键设置不同'

  when a.类型<>b.类型 then N'字段类型不同'

  when a.占用字节数<>b.占用字节数 then N'占用字节数'

  when a.长度<>b.长度 then N'长度不同'

  when a.小数位数<>b.小数位数 then N'小数位数不同'

  when a.允许空<>b.允许空 then N'是否允许空不同'

  when a.默认值<>b.默认值 then N'默认值不同'

  when a.字段说明<>b.字段说明 then N'字段说明不同'

 else '' end,

 *

 from #tb1 a

 full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名

where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null 

 or a.标识<>b.标识 or a.主键<>b.主键 or a.类型<>b.类型

 or a.占用字节数<>b.占用字节数 or a.长度<>b.长度 or a.小数位数<>b.小数位数

 or a.允许空<>b.允许空 or a.默认值<>b.默认值 or a.字段说明<>b.字段说明

order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)



GO



--TEST:

exec [up_compareDBstructure]

	'test',

	'csdn'