SQL Serverでクエリを使ってテーブルの列情報を取得する

SQL Server 2000以降ならつかえたはず。
知っといて損はないクエリ。

意外とこういうことできることを知らない人が多い気がする。
…うちの会社はね。

後付でテーブル設計書作るときに必要な情報も大抵はこれでOKかと。

SELECT
    [COL].TABLE_NAME                            AS [テーブル名]
,   [COL].ORDINAL_POSITION                      AS [列番]
,   CASE
        WHEN [KEY].CONSTRAINT_NAME IS NULL THEN ''
        ELSE 'PK'
    END                                         AS [主キー]
,   CASE
        WHEN COLUMNPROPERTY(
                OBJECT_ID(QUOTENAME([COL].TABLE_SCHEMA) + '.' + QUOTENAME([COL].TABLE_NAME))
            ,   [COL].COLUMN_NAME
            ,   'IsIdentity') = 0 THEN ''
        ELSE 'あり' 
    END                                         AS [自動発番]
,   [COL].COLUMN_NAME                           AS [列名]
,   [COL].DATA_TYPE                             AS [データ型]
,   CASE
        WHEN [COL].DATA_TYPE LIKE '%char' THEN
            CASE [COL].CHARACTER_MAXIMUM_LENGTH
                WHEN -1 THEN 'MAX'
                WHEN NULL THEN ''
                ELSE ISNULL(CONVERT(VARCHAR(10), [COL].CHARACTER_MAXIMUM_LENGTH), '')
            END
        WHEN [COL].DATA_TYPE IN ('decimal', 'numeric') THEN
            CONVERT(varchar, [COL].NUMERIC_PRECISION) + ',' + CONVERT(varchar, [COL].NUMERIC_SCALE)
        ELSE ISNULL(CONVERT(varchar, [COL].NUMERIC_PRECISION), '---')
    END                                         AS [データサイズ]
,   CASE
        WHEN [COL].IS_NULLABLE = 'NO' THEN 'NG'
        ELSE 'OK'
    END                                         AS [NULL許容]
,   ISNULL([COL].COLUMN_DEFAULT, '')            AS [DEFAULT]
FROM
    INFORMATION_SCHEMA.COLUMNS [COL]
LEFT OUTER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE [KEY]
    ON  [COL].TABLE_NAME = [KEY].TABLE_NAME
    AND [COL].COLUMN_NAME = [KEY].COLUMN_NAME
WHERE
    1 = 1
--AND [COL].TABLE_NAME = ''
--AND [COL].COLUMN_NAME LIKE '%Kingaku%'
ORDER BY
    [COL].TABLE_NAME
,   [COL].ORDINAL_POSITION