获取表的字段的属性信息

SELECT 列名,型,桁,小数桁 FROM
(
SELECT
(SELECT name FROM SYS.SCHEMAS WHERE T.SCHEMA_ID = SCHEMA_ID) スキーマ,
T.name AS 表名,
C.column_id AS 列番,
C.name AS 列名,
Y.name AS 型,
CASE
WHEN Y.name IN ('nvarchar', 'nchar') THEN C.max_length / 2
WHEN C.precision = 0 THEN C.max_length
ELSE C.precision
END AS 桁,
C.scale AS 小数桁,
C.max_length as [サイズ(バイト)],
C.is_nullable AS Null可,
(SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.column_id = C.column_id and C.object_id = ic.object_id and exists (SELECT * FROM SYS.KEY_CONSTRAINTS kc WHERE kc.type = 'PK' and kc.parent_object_id = T.object_id and kc.unique_index_id = ic.index_id )) AS [PK],
ep.value as 説明
FROM sys.tables AS T INNER JOIN
sys.columns AS C ON T.object_id = C.object_id INNER JOIN
sys.types AS Y ON C.system_type_id = Y.system_type_id AND C.user_type_id = Y.user_type_id
LEFT OUTER JOIN
sys.extended_properties AS ep ON C.object_id = ep.major_id and C.column_id = ep.minor_id
) z
WHERE z.表名=@TableName --テーブル名
ORDER BY z.スキーマ, z.表名, z.列番;