T-SQL テーブル定義の取得 (Temporal Table / Graph Table 対応版)

Temporal Table と Graph Table を考慮したテーブル定義の取得クエリ。
SELECT 
  schemas.name as SCHEMA_NAME,
  object_name(cols.object_id) as TABLE_NAME, 
  case 
    when cols.graph_type in (2, 5, 8) then SUBSTRING(cols.name, 0, PATINDEX('%[_]%', SUBSTRING(cols.name, PATINDEX('%[_]%', cols.name) + 1, 100)) + PATINDEX('%[_]%', cols.name))
    else cols.name 
  end as COLUMN_NAME, 
  cols.is_nullable AS IS_NULLABLE, 
  types.name as DATA_TYPE, 
  case 
    when keys.column_id is not null then CONVERT(BIT, 1)
    else convert(bit, 0)
  end as IS_KEY, 
  case 
    when types.name in ('char', 'varchar') then cols.max_length 
    when types.name in ('nchar', 'nvarchar') and cols.max_length > 0 then cols.max_length / 2
    else -1 end as MAX_LENGTH
FROM 
  sys.columns AS cols inner join sys.tables tables on (
    cols.object_id = tables.object_id
    and tables.type = 'U'
  ) inner join sys.schemas schemas on (
    tables.schema_id = schemas.schema_id
  ) join sys.types types on ( 
    cols.system_type_id = types.system_type_id
    and cols.user_type_id = types.user_type_id
  ) LEFT OUTER JOIN ( 
    select ix.object_id, ic.column_id
    from 
      sys.indexes ix 
      inner join sys.index_columns ic on ix.object_id = ic.object_id and ix.index_id = ic.index_id
    where ix.is_primary_key = 1
  ) AS keys ON ( 
    cols.object_id = keys.object_id
    and cols.column_id  = keys.column_id
  ) 
WHERE 
  cols.is_hidden = 0
  and tables.temporal_type <> '1'
ORDER BY 
  schemas.name,
  tables.name, 
  cols.column_id