将SQLSERVER中所有表的列信息显示出来

将SQLSERVER中所有表的列信息显示出来

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