SQL获取表定义

查询某个库里所有表的定义，包括字段名，值类型，长度，是否为空，是否为主键等

 

SELECT  SysObjects.Name  as  tb_name, SysColumns.Name  as   col_name , SysTypes.Name  as  col_type, SysColumns.Length  as  col_len,  isnull (SysProperties.Value,SysColumns.Name)  as  col_memo,
case   when  SysColumns.name  in  
 ( select   ' 主键 ' = a.name 
  FROM  syscolumns a  inner   join  sysobjects b  on  a.id = b.id    and  b.xtype = ' U '   and   b.name <> ' dtproperties '
   where   exists
  ( SELECT   1   FROM  sysobjects  where  xtype = ' PK '   and  name  in  
   ( SELECT  name  FROM  sysindexes  WHERE  indid  in
    ( SELECT  indid  FROM  sysindexkeys  WHERE  id  =  a.id  AND  colid = a.colid
    )
   )
  ) 
  and   b.name = SysObjects.Name)
then   1   else   0   end   as  is_key

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  SysTypes.Name  <>   ' sysname '   AND  Sysobjects.Name  Like   ' % '   ORDER   By  SysObjects.Name, SysColumns.colid 

将最后一个“%”号改为某个数据表名，即是查看该表的定义