--注明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