維護數字字典時，大量匯入資料庫的列注釋、類型等相關資訊

標籤：style   blog   http   color   資料   ar   2014   div   

匯出的格式形如：

 

 

use ppdai_vd;goSELECT  ‘ppdai_vd‘ AS 資料庫名,    a.name as ‘表名‘ ,    isnull(e.value,‘‘) as ‘表注釋‘,    CONVERT(VARCHAR(19), a.create_date ,23) AS ‘建立時間‘,    b.column_id as ‘欄位序號‘,    b.name as ‘欄位名‘,    ‘資料類型‘ =      case        when c.name IN (‘varchar‘,‘nvarchar‘,‘varbinary‘) and b.max_length =-1        then c.name +‘(max)‘       when c.name in (‘varchar‘,‘nvarchar‘,‘char‘,‘nchar‘,‘binary‘,‘varbinary‘) and b.max_length>0        then c.name +‘(‘+CAST(b.max_length AS VARCHAR)+‘)‘        else c.name        end        ,isnull(d.[value],‘‘) as ‘欄位注釋‘    FROM  sys.objects a inner join  sys.columns b on  a.object_id=b.object_id and a.type=‘U‘  left join   sys.types c   on  b.user_type_id = c.user_type_idleft join   sys.extended_properties d on b.object_id=d.major_id and b.column_id=d.minor_idleft join   sys.extended_properties e on a.object_id =e.major_id and e.minor_id=0--where a.object_id = object_id(‘user‘)     order by a.name,b.column_id