【訂正版】 テーブル定義の取得 - お だ のスペース の改訂版
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