查看指定数据表的字段信息(SQL版)

SELECT  
  ( case   when  a.colorder = 1   then  d.name  else   ''   end ) 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 = ' INVMB '   /**/ /*Name of Your Table*/

  order   by   object_name (a.id),a.colorder

转载于:https://www.cnblogs.com/safezone/articles/1263686.html