請問各位高手,若要比對兩個table結構是否一樣,是否有指令可用,sql是否有統計table欄位總長度的指令?謝謝!!
創作者介紹
創作者 vbqa 的頭像
vbqa

小吳 VBQA 程式設計討論

vbqa 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • 阿利
  • 以下是我的回答 ...:p
    還有關 SQL Server 或 Crystal Report 的問題或是其他資料庫的問題.
    歡迎到新開版的 http://cu29.sytes.net 討論園區中發表.
    如果我會一定會很快的回答您.. : )
    1.比較的 TaBLE 為 t1 及 t2
    select c.name as t1,d.name as t2,c.xtype,d.xtype,c.prec,d.prec,c.scale,d.scale from syscolumns c inner join syscolumns d on (c.name=d.name and(c.xtype!=d.xtype or c.prec!=d.prec or c.scale!=d.scale))
    where c.id= object_id('t1') and d.id= object_id('t2')
    union
    select a.name as t1,'' as t2,a.xtype,'' as xtype,a.prec,'' as prec ,a.scale, '' as scale from syscolumns a,syscolumns b where a.id=object_id('t1') and b.id=object_id('t2') and a.name not in(select name from syscolumns where id=object_id('t2')) group by a.name,a.xtype,a.prec,a.scale
    union
    select '' as t1,a.name as t2,a.xtype,'' as xtype,a.prec,'' as prec ,a.scale, '' as scale from syscolumns a,syscolumns b where a.id=object_id('t2') and b.id=object_id('t1') and a.name not in(select name from syscolumns where id=object_id('t1')) group by a.name,a.xtype,a.prec,a.scale

    2.求某 Table 一列所有欄位之總長度
    select sum(length) from syscolumns where id=object_id('t1')

  • marty
  • 阿利謝謝你囉!!