MS SQL新旧库数据字典比较脚本

/*MS SQL新旧库数据字典比较脚本*/
--注明1：新旧库必须在同一数据库服务器同一实例中,最好以Sa身份登入。
--注明2：本脚本可作为系统升级改造，得到相关信息后作数据迁移之用。
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