sqlserver 获取表内字段的基础信息

SELECT
B.table_name,
B.column_name,
C.value AS column_description,
B.column_type,
B.is_nullable,
D.definition AS default_value
FROM
(
SELECT
A.name AS table_name,
B.name AS column_name,
TYPE_NAME(B.system_type_id) +
CASE
WHEN B.system_type_id IN (231, 239) THEN '(' + CAST(B.max_length / 2 AS VARCHAR) + ')'
WHEN B.system_type_id IN (167, 175, 231, 239) THEN ' COLLATE ' + B.collation_name
ELSE ''
END AS column_type,
B.is_nullable,
ROW_NUMBER() OVER (PARTITION BY A.name ORDER BY B.column_id) AS rn
FROM
sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
WHERE
A.name = '此处替换为表名'
) B
LEFT JOIN sys.extended_properties C ON C.major_id = OBJECT_ID(B.table_name) AND C.minor_id = COLUMNPROPERTY(OBJECT_ID(B.table_name), B.column_name, 'ColumnID')
LEFT JOIN sys.default_constraints D ON D.parent_object_id = OBJECT_ID(B.table_name) AND OBJECT_NAME(D.parent_object_id) = B.table_name AND COL_NAME(D.parent_object_id, D.parent_column_id) = B.column_name
ORDER BY
B.rn;