日期: 2018 年 6 月 5 日

1.Table清單

SELECT sys.objects.name AS TableName, ep.name AS PropertyName,
       ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

2.Table Schema

SELECT a.Table_schema +'.'+a.Table_name   as Table名稱   
       ,b.COLUMN_NAME                     as Table名稱   
       ,b.DATA_TYPE                       as 資料型別   
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 欄位長度   
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值   
       ,b.IS_NULLABLE                         as 允許空值否
       ,( SELECT value   
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
          WHERE name='MS_Description' and objtype='COLUMN'    
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
        ) as 欄位描述   
FROM INFORMATION_SCHEMA.TABLES  a   
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION