【SQL】列出資料表的所有細項

方便修改與調整型別等

SELECT
     a.TABLE_NAME                as 表格名稱,
     b.COLUMN_NAME               as 欄位名稱,
     b.DATA_TYPE                 as 資料型別,
     b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,
     b.COLUMN_DEFAULT            as 預設值,
     b.IS_NULLABLE               as 允許空值,
     (
         SELECT
             value
         FROM
             fn_listextendedproperty (NULL, 'schema', 'dbo', '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, ordinal_position

發表評論

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

二 × 1 =

購物車