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


阅读本文前我们首先需要了解新库和旧库一定要在同一个数据库服务器的同一实例中,您最好以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



相关阅读:
mysql_fetch_row,mysql_fetch_array,mysql_fetch_assoc的区别
JavaScript与JavaApplet综合运用
DBA应当如何查看Oracle数据库连接情况
vista下查看本机地址的IPv6形式
Linux系统Iptables规则执行顺序详解
vbs整人代码大集合 多年的代码收集
PHP 截取字符串专题集合
在不安装Oracle客户端的情况下进行系统移植
dedecms文章列表实现序列号排序效果实现代码
巧用SQL链接服务器访问远程Access数据库
C#编写方法实例
技巧解析 利用Linux命令关闭系统的方法
socket与TcpListener/TcpClient/UdpClient 的区别及联系
黑客选择CSS代码挂马 CSS挂马攻防实录
快速导航

Copyright © 2016 phpStudy |