IT资讯门户,互联网技术中心

SQL Server新库和旧库数据字典的比较

来源:未知 作者:admin 更新时间:2010-11-19 11:51 点击:
阅读本文前我们首先需要了解新库和旧库一定要在同一个数据库服务器的同一实例中,您最好以sa的身份来登陆,下面的这个实例脚本可以用于用于您的系统升级改造,在得到相关信息后来作数据的迁移。 阅读本文前我们首先需要了解新库和旧库一定要在

阅读本文前我们首先需要了解新库和旧库一定要在同一个数据库服务器的同一实例中,您最好以sa的身份来登陆,下面的这个实例脚本可以用于用于您的系统升级改造,在得到相关信息后来作数据的迁移。

阅读本文前我们首先需要了解新库和旧库一定要在同一个数据库服务器的同一实例中,您最好以sa的身份来登陆,下面的这个实例脚本可以用于用于您的系统升级改造,在得到相关信息后来作数据的迁移。

declare @i int

set @i=4

/*3:1是需要得到新库增加的数据字典信息。

2是需要得到旧库多出的数据字典信息。

3是需要得到新库增加的表的数据字典信息。

4是需要得到旧库多出的表的数据字典信息*/

use temp --打开旧库

SELECT sysobjects.name AS [table], CASE WHEN CAST(sysproperties.[value] AS varchar)

IS NULL THEN '' ELSE CAST(sysproperties.[value] AS varchar) END AS 表说明,

syscolumns.name AS field, CASE WHEN CAST(properties.[value] AS varchar) IS NULL

THEN '' ELSE CAST(properties.[value] AS varchar) END AS 字段说明,

systypes.name AS type, syscolumns.length,

ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0)

AS 小数位数, syscolumns.isnullable AS isnull,

CASE WHEN syscomments.text IS NULL

THEN '' ELSE syscomments.text END AS [Default],

CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')

= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = syscolumns.id AND colid = syscolumns.colid)))

THEN '√' ELSE '' END AS 主键 into #old

FROM syscolumns INNER JOIN

sysobjects ON sysobjects.id = syscolumns.id INNER JOIN

systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN

sysproperties properties ON syscolumns.id = properties.id AND

syscolumns.colid = properties.smallid LEFT OUTER JOIN

sysproperties ON sysobjects.id = sysproperties.id AND

sysproperties.smallid = 0 LEFT OUTER JOIN

syscomments ON syscolumns.cdefault = syscomments.id

WHERE (sysobjects.xtype = 'U')

use accdb --打开新库

SELECT sysobjects.name AS [table], CASE WHEN CAST(sysproperties.[value] AS varchar)

IS NULL THEN '' ELSE CAST(sysproperties.[value] AS varchar) END AS 表说明,

syscolumns.name AS field, CASE WHEN CAST(properties.[value] AS varchar) IS NULL

THEN '' ELSE CAST(properties.[value] AS varchar) END AS 字段说明,

systypes.name AS type, syscolumns.length,

ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0)

AS 小数位数, syscolumns.isnullable AS isnull,

CASE WHEN syscomments.text IS NULL

THEN '' ELSE syscomments.text END AS [Default],

CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')

= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = syscolumns.id AND colid = syscolumns.colid)))

THEN '√' ELSE '' END AS 主键 into #new

FROM syscolumns INNER JOIN

sysobjects ON sysobjects.id = syscolumns.id INNER JOIN

systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN

sysproperties properties ON syscolumns.id = properties.id AND

syscolumns.colid = properties.smallid LEFT OUTER JOIN

sysproperties ON sysobjects.id = sysproperties.id AND

sysproperties.smallid = 0 LEFT OUTER JOIN

syscomments ON syscolumns.cdefault = syscomments.id

WHERE (sysobjects.xtype = 'U')

if @i=1

begin

select n.* --新库与旧库相比较后新库增加的数据字典信息

from #new n left join #old o on n.[table]=o.[table] and n.field=o.field where o.[table] is null

or o.field is null order by n.[table],n.field

end

else

begin

if @i=2

begin

select o.* --新库与旧库相比较后旧库多出的数据字典信息

from #new n right join #old o on n.[table]=o.[table] and n.field=o.field where n.[table] is null

or n.field is null order by o.[table],o.field

end

else

begin

if @i=3

begin

select * --新库与旧库相比较后新库增加的表的数据字典信息

from #new where [table] <> all(select [table] from #old ) order by [table],field

end

else

begin

if @i=4

begin

select * --新库与旧库相比较后旧库多出的表的数据字典信息

from #old where [table] <> all(select [table] from #new ) order by [table],field

end

else

begin

select '出错啦'

end

end

end

end

drop table #old

drop table #new

Tag:
  • [智多PC网]刊登此文只为传递信息,并不表示赞同或者反对作者观点。
  • 如果此内容给您造成了负面影响或者损失,本站不承担任何责任。
  • 如果内容涉及版权问题,请及时与我们取得联系。

文章评论