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

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

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

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

view plainprint?

1. SELECT  
2.     [COL].TABLE_NAME                            AS [テーブル名]  
3. ,   [COL].ORDINAL_POSITION                      AS [列番]  
4. ,   CASE  
5.         WHEN [KEY].CONSTRAINT_NAME IS NULL THEN ''  
6.         ELSE 'PK'  
7.     END                                         AS [主キー]  
8. ,   CASE  
9.         WHEN COLUMNPROPERTY(  
10.                 OBJECT_ID(QUOTENAME([COL].TABLE_SCHEMA) + '.' + QUOTENAME([COL].TABLE_NAME))  
11.             ,   [COL].COLUMN_NAME  
12.             ,   'IsIdentity') = 0 THEN ''  
13.         ELSE 'あり'   
14.     END                                         AS [自動発番]  
15. ,   [COL].COLUMN_NAME                           AS [列名]  
16. ,   [COL].DATA_TYPE                             AS [データ型]  
17. ,   CASE  
18.         WHEN [COL].DATA_TYPE LIKE '%char' THEN  
19.             CASE [COL].CHARACTER_MAXIMUM_LENGTH  
20.                 WHEN -1 THEN 'MAX'  
21.                 WHEN NULL THEN ''  
22.                 ELSE ISNULL(CONVERT(VARCHAR(10), [COL].CHARACTER_MAXIMUM_LENGTH), '')  
23.             END  
24.         WHEN [COL].DATA_TYPE IN ('decimal', 'numeric') THEN  
25.             CONVERT(varchar, [COL].NUMERIC_PRECISION) + ',' + CONVERT(varchar, [COL].NUMERIC_SCALE)  
26.         ELSE ISNULL(CONVERT(varchar, [COL].NUMERIC_PRECISION), '---')  
27.     END                                         AS [データサイズ]  
28. ,   CASE  
29.         WHEN [COL].IS_NULLABLE = 'NO' THEN 'NG'  
30.         ELSE 'OK'  
31.     END                                         AS [NULL許容]  
32. ,   ISNULL([COL].COLUMN_DEFAULT, '')            AS [DEFAULT]  
33. FROM  
34.     INFORMATION_SCHEMA.COLUMNS [COL]  
35. LEFT OUTER JOIN  
36.     INFORMATION_SCHEMA.KEY_COLUMN_USAGE [KEY]  
37.     ON  [COL].TABLE_NAME = [KEY].TABLE_NAME  
38.     AND [COL].COLUMN_NAME = [KEY].COLUMN_NAME  
39. WHERE  
40.     1 = 1  
41. --AND [COL].TABLE_NAME = ''  
42. --AND [COL].COLUMN_NAME LIKE '%Kingaku%'  
43. ORDER BY  
44.     [COL].TABLE_NAME  
45. ,   [COL].ORDINAL_POSITION  

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