取数据库表中字段的描述信息

Sql2000:
==========================

 

SELECT  SysObjects.Name,  -- 表名 
    SysColumns.Name,   -- 字段名
    SysTypes.Name,   -- 字段类型
    SysColumns.Length,   -- 字段长度  
    SysProperties.Value   -- 字段描述   
   FROM  SysObjects,   
  SysTypes,   
  SysColumns   
   LEFT   JOIN  SysProperties 
   ON  (Syscolumns.Id    =    Sysproperties.Id    AND    Syscolumns.Colid  =  Sysproperties.Smallid)   
   WHERE  (Sysobjects.Xtype    = ' u '   OR  Sysobjects.Xtype    = ' v ' )   
   AND  Sysobjects.Id  =  Syscolumns.Id   
   AND  SysTypes.XType  =  Syscolumns.XType   
   AND  Sysobjects.Name  =   ' Contract '   -- 指字要查找的表名
   ORDER   BY  smallid 

SqlServer2005:
=============================

SELECT  
    表名        =   case   when  a.colorder = 1   then  d.name  else   ''   end ,
    表说明      =   case   when  a.colorder = 1   then   isnull (f.value, '' )  else   ''   end ,
    字段序号    =  a.colorder,
    字段名      =  a.name,
    标识        =   case   when   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   then   ' √ ' else   ''   end ,
    主键        =   case   when   exists ( SELECT   1   FROM  sysobjects  where  xtype = ' PK '   and  parent_obj = a.id  and  name  in  (
                      SELECT  name  FROM  sysindexes  WHERE  indid  in (  SELECT  indid  FROM  sysindexkeys  WHERE  id  =  a.id  AND  colid = a.colid)))  then   ' √ '   else   ''   end ,
    类型        =  b.name,
    占用字节数  =  a.length,
    长度        =   COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ),
    小数位数    =   isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ),
    允许空      =   case   when  a.isnullable = 1   then   ' √ ' else   ''   end ,
    默认值      =   isnull (e. text , '' ),
    字段说明    =   isnull (g. [ value ] , '' )
FROM  
    syscolumns a
left   join  
    systypes b 
on  
    a.xusertype = 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  
sys.extended_properties   g 
on  
    a.id = G.major_id  and  a.colid = g.minor_id  
left   join  

sys.extended_properties f
on  
    d.id = f.major_id  and  f.minor_id = 0
where  
    d.name = ' Contract '      -- 如果只查询指定表,加上此条件
order   by  
    a.id,a.colorder

转载于:https://www.cnblogs.com/ztotem/archive/2008/12/23/1360504.html