【訂正版】 テーブル定義の取得

※2018/01/20 に書き換え
T-SQL テーブル定義の取得 (Temporal Table / Graph Table 対応版) - お だ のスペース

以前、Windows Live のブログに載せていたクエリに誤りがある事に今日気付きました。
テーブル定義の取得 | お だ のスペース

引用

SELECT 
  cols.TABLE_NAME, 
  cols.COLUMN_NAME, 
  CASE cols.IS_NULLABLE 
    WHEN 'YES' THEN CONVERT(BIT, 1) 
    ELSE CONVERT(BIT, 0) 
  END AS IS_NULLABLE, 
  cols.DATA_TYPE, 
  keys.IS_KEY, 
  identities.IS_IDENTITY, 
  ISNULL(cols.CHARACTER_MAXIMUM_LENGTH, -1) 
FROM 
  INFORMATION_SCHEMA.COLUMNS AS cols LEFT OUTER JOIN ( 
    SELECT 
      *, 
      CONVERT(BIT, 1) AS IS_KEY 
    FROM 
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  ) AS keys ON ( 
    cols.TABLE_NAME = keys.TABLE_NAME AND 
    cols.COLUMN_NAME = keys.COLUMN_NAME 
  ) LEFT OUTER JOIN ( 
    SELECT 
      o.name AS TABLE_NAME, 
      c.name AS COLUMN_NAME, 
      CONVERT(BIT, 1) AS IS_IDENTITY 
    FROM 
      syscolumns c INNER JOIN sysobjects o ON ( 
        c.id = o.id 
      ) 
    WHERE 
      c.status >= 128 
  ) AS identities ON ( 
    cols.TABLE_NAME = identities.TABLE_NAME AND 
    cols.COLUMN_NAME = identities.COLUMN_NAME 
  ) 
WHERE 
  cols.TABLE_NAME = @table_name 
ORDER BY 
  cols.TABLE_NAME, 
  cols.ORDINAL_POSITION 

このクエリですが、FOREIGN KEY や UNIQUE 制約を指定していると正しく取得出来ません。
原因は、INFORMATION_SCHEMA.KEY_COLUMN_USAGE から FOREIGN KEY や UNIQUE 制約のデータも取得出来るからです。
当時この事に気付かずに公開してしまいました。そこで改めて訂正版を公開します。

SELECT 
  cols.TABLE_NAME, 
  cols.COLUMN_NAME, 
  CASE cols.IS_NULLABLE 
    WHEN 'YES' THEN CONVERT(BIT, 1) 
    ELSE CONVERT(BIT, 0) 
  END AS IS_NULLABLE, 
  cols.DATA_TYPE, 
  keys.IS_KEY, 
  identities.IS_IDENTITY, 
  ISNULL(cols.CHARACTER_MAXIMUM_LENGTH, -1) AS MAX_LENGTH 
FROM 
  INFORMATION_SCHEMA.COLUMNS AS cols LEFT OUTER JOIN ( 
    SELECT 
      K.*, 
      CONVERT(BIT, 1) AS IS_KEY 
    FROM 
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
    WHERE 
      EXISTS ( 
        SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
        WHERE 
          K.CONSTRAINT_NAME = T.CONSTRAINT_NAME AND 
          T.CONSTRAINT_TYPE = 'PRIMARY KEY'
      ) 
  ) AS keys ON ( 
    cols.TABLE_NAME = keys.TABLE_NAME AND 
    cols.COLUMN_NAME = keys.COLUMN_NAME 
  ) LEFT OUTER JOIN ( 
    SELECT 
      o.name AS TABLE_NAME, 
      c.name AS COLUMN_NAME, 
      CONVERT(BIT, 1) AS IS_IDENTITY 
    FROM 
      syscolumns c INNER JOIN sysobjects o ON ( 
        c.id = o.id 
      ) 
    WHERE 
      c.status >= 128 
  ) AS identities ON ( 
    cols.TABLE_NAME = identities.TABLE_NAME AND 
    cols.COLUMN_NAME = identities.COLUMN_NAME 
  ) 
WHERE 
  cols.TABLE_NAME = @tableName
ORDER BY 
  cols.TABLE_NAME, 
  cols.ORDINAL_POSITION 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS を使用して、INFORMATION_SCHEMA.KEY_COLUMN_USAGE から PRIMARY KEY のデータだけを抽出する様にしました。

今回、システム情報スキーマ ビュー (TRANSACT-SQL) | Microsoft Docs を使っていますが、実は オブジェクトのカタログ ビュー (TRANSACT-SQL) | Microsoft Docs を使った方が簡単かも知れません。今度試してみます。