五顆星星的故事

-- 取得 Table 註解
SELECT
  sysobjects.name AS tableName,
  sysproperties.value AS comment
FROM
  sysobjects
INNER JOIN
  sysproperties
    ON sysobjects.id = sysproperties.id
WHERE
  sysproperties.smallid = 0
;
-- 取得 Column 註解
SELECT
  sysobjects.name AS tableName,
  syscolumns.name AS columnName,
  sysproperties.value AS comment
FROM
  syscolumns
INNER JOIN
  sysproperties
    ON syscolumns.id = sysproperties.id
    AND syscolumns.colid = sysproperties.smallid
INNER JOIN
  sysobjects
    ON syscolumns.id = sysobjects.id
;

--

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