sqlserver获取所有表的字段说明

--获取所有表的字段说明

SELECT t.[name] AS 表名,c.[column_id] AS 字段ID,c.[name] AS 字段名,tp.[name] AS 字段类型,c.[max_length] AS 字段长度,
cast(ep.[value] as varchar(100)) AS [字段说明]
FROM sys.tables AS t
INNER JOIN sys.columns
AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
LEFT JOIN sys.types AS tp
ON tp.user_type_id = c.user_type_id;

--获取单表的字段说明

SELECT t.[name] AS 表名,c.[column_id] AS 字段ID,c.[name] AS 字段名,tp.[name] AS 字段类型,c.[max_length] AS 字段长度,
cast(ep.[value] as varchar(100)) AS [字段说明]
FROM sys.tables AS t
INNER JOIN sys.columns
AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
LEFT JOIN sys.types AS tp
ON tp.user_type_id = c.user_type_id
where t.name = '替换表名';