SQL Server获取表字段描述

获取数据库中表字段的描述 

SELECT A.name AS table_name,B.name AS column_name,cp.value AS column_description
	FROM sys.tables A INNER JOIN sys.columns B
	ON B.object_id = A.object_id LEFT JOIN sys.extended_properties cp
	ON cp.major_id = B.object_id AND cp.minor_id = B.column_id

上述SQL中可以直接运行，但是当把 SQL 放到 jdbcTemplate 中执行时报一下错误：

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.

是因为 sys.extended_properties 表的 value 属性的 TYPE_NAME 是 sql_variant 类型的；

解决的方法：使用 CONVERT 函数将该属性转成 varchar 类型。

CONVERT(varchar(200), cp.value)

修改后的SQL为：

SELECT A.name AS table_name,B.name AS column_name, CONVERT(varchar(200), cp.value) AS column_description
	FROM sys.tables A INNER JOIN sys.columns B
	ON B.object_id = A.object_id LEFT JOIN sys.extended_properties cp
	ON cp.major_id = B.object_id AND cp.minor_id = B.column_id

附查询数据库中所有的表和列信息：

SELECT * FROM INFORMATION_SCHEMA.COLUMNS