一个用于分析表结构的查询语句和几个系统存储过程

 

SELECT  ( CASE   WHEN  a.colorder  =   1   THEN  d .name  ELSE   ''   END ) N ' TableName ' ,
    a.colorder N ' SortID ' , a.name N ' FieldName ' ,
    ( CASE   WHEN   COLUMNPROPERTY (a.id,a.name,  ' IsIdentity ' )  =   1   THEN   ' √ '   ELSE   ''   END ) N ' IsIdentity ' ,
    ( 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 ' IsKey ' , 
    b.name N ' ColType ' , 
    a.length N ' Bits ' , 
     COLUMNPROPERTY (a.id, a.name,  ' PRECISION ' )  AS  N ' Length ' , 
     IsNull ( COLUMNPROPERTY (a.id, a.name,  ' Scale ' ),  0 )  AS  N ' Scale ' , 
    ( CASE   WHEN  a.isnullable  =   1   THEN   ' √ '   ELSE   ''   END ) N ' IsNullAble ' , 
     isnull (e. text ,  '' ) N ' Default ' ,
     isnull (g. [ value ] ,  '' )  AS  N ' Description '

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 '   LEFT   JOIN
    syscomments e  ON  a.cdefault  =  e.id  LEFT   JOIN
    sysproperties g  ON  a.id  =  g.id  AND  a.colid  =  g.smallid
ORDER   BY   object_name (a.id), a.colorder

sp_MStablekeys ：列出对应表的Primary Key
sp_MSTableChecks:列出对应表的check(约束）
sp_msHelpIndex:列出对应表的索引信息
sp_MShelpcolumns:列出对应表或视图的各列信息
sp_MSdependencies:列出相依赖的对象
sp_MStablerefs:列出外键
sp_MStablespace:列出表记录的数量和占用的空间

转载于:https://www.cnblogs.com/Hedonister/archive/2005/10/25/261675.html