存储过程得到某个表的所有字段信息

ALTER    procedure  GetTableColumns 
@TableName   varchar ( 100 )
as
begin
-- ---------------开始
SELECT  
  d.name N ' 表名 ' ,
 a.colorder N ' 字段序号 ' ,
 a.name N ' 字段名 ' ,
 ( case   when   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   then   ' √ ' else   ''   end ) N ' 标识 ' ,
 ( case   when  ( SELECT   count ( * )
FROM  sysobjects
  WHERE  (name  in
           ( SELECT  name
           FROM  sysindexes
           WHERE  (id  =  a.id)  AND  (indid  in
                    ( SELECT  indid
                    FROM  sysindexkeys
                    WHERE  (id  =  a.id)  AND  (colid  in
                             ( SELECT  colid
                             FROM  syscolumns
                             WHERE  (id  =  a.id)  AND  (name  =  a.name)))))))  AND
        (xtype  =   ' PK ' )) > 0   then   ' √ '   else   ''   end ) N ' 主键 ' ,
 b.name N ' 类型 ' ,
 a.length N ' 占用字节数 ' ,
  COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )  as  N ' 长度 ' ,
  isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )  as  N ' 小数位数 ' ,
 ( case   when  a.isnullable = 1   then   ' √ ' else   ''   end ) N ' 允许空 ' ,
  isnull (e. text , '' ) N ' 默认值 ' ,
  isnull (g. [ value ] , '' )  AS  N ' 字段说明 '
-- into ##tx

FROM   syscolumns  a  left   join  systypes b 
on   a.xtype = b.xusertype
inner   join  sysobjects d 
on  a.id = d.id   and   d.xtype = ' U '   and   d.name <> ' dtproperties '
left   join  syscomments e
on  a.cdefault = e.id
left   join  sysproperties g
on  a.id = g.id  AND  a.colid  =  g.smallid  where  d.name = @TableName
order   by   object_name (a.id),a.colorder
-- --------------------------------------------结束
end

转载于:https://www.cnblogs.com/heimirror/archive/2008/06/24/1228659.html