Jason's 程設BLOG

--取得所有欄位定義的方法(含備註欄位)
--方法1:
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

--方法2:
SELECT tab.name                   table_name, 
       col.colid                  column_id, 
       col.name                   column_name, 
       typ.name                   data_type, 
       col.prec                   PRECISION, 
       col.scale                  scale, 
       col.length, 
       com.TEXT                   default_value, 
       CASE 
         WHEN col.isnullable = 1 THEN 'Y' 
         ELSE 'N' 
       END                        is_nullable, 
       CASE 
         WHEN col.status & 0X80 = 0X80 THEN 'Y' 
         ELSE 'N' 
       END                        is_identity, 
       (SELECT VALUE 
        FROM   Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 
               tab.name, 
               'column', 
                       col.name)) DESCRIPTION 
FROM   sysobjects tab, 
       syscolumns col 
       LEFT OUTER JOIN syscomments com 
                       INNER JOIN sysobjects obj 
                         ON com.id = obj.id 
         ON col.cdefault = com.id 
            AND com.colid = 1, 
       systypes typ 
WHERE  tab.id = col.id 
       AND tab.xtype = 'U' 
       AND col.xusertype = typ.xusertype