【转】MSSQL获取指定表的列名信息，描述，数据类型，长度

/*
--作用：根据特定的表名查询出字段，以及描述，数据类型，长度，精度，是否自增，是否为空等信息
--作者：wonder QQ:37036846 QQ群:.NET顶级精英群 ID：124766907
--时间：2011-03-23 11:25
--描述：创建存储过程
--参数：@tableName  表名
*/
CREATE PROC sp_GetListsColumnInfoByTableName(
@tableName nvarchar(255))
AS 
BEGIN

SELECT CASE  WHEN Q.INDID >=1 then '主键' ELSE '' END IS_KEYS,
x.objname as ColumnName,x.value as ColumnDescription,
z.name as DataType,y.max_length as length ,y.precision,y.scale,y.is_identity,y.is_nullable
FROM
(
SELECT *FROM ::fn_listextendedproperty (NULL,   'user ',   'dbo ',   'table ', @tableName,   'column ',   default) 
) AS X 
INNER JOIN 
(
SELECT *FROM  sys.all_columns 
where object_id=(select object_id from sys.all_objects where name=@tableName)
) AS Y ON X.objname=y.Name collate Chinese_PRC_CI_AS
inner join sys.systypes Z
ON  Z.xusertype=Y.user_type_id 
left join (select *from  sysindexkeys where id=(select object_id from sys.all_objects where name=@tableName)) as Q
ON Q.colid=y.COLUMN_ID
order by y.Column_id
END
测试存储过程：

EXEC sp_GetListsColumnInfoByTableName 'userInfo'

转自：http://www.cnblogs.com/wanzegui325/archive/2011/03/23/GetListsColumnInfoByTableName.html