转载：SQL SERVER 系统表应用

http://www.cnblogs.com/sandy_liao/admin/EditPosts.aspx?opt=1

0.查询表字段的标题备注

 SELECT  A.COLID, UPPER(A.NAME) AS NAME,ISNULL(C.VALUE,A.NAME) AS REMARK , UPPER(B.NAME) AS DATATYPE,
2   (CASE WHEN A.XPREC=0 THEN A.LENGTH ELSE A.XPREC END) AS XPREC,
3   A.XSCALE, A.ISNULLABLE,A.CDEFAULT
4  FROM SYSCOLUMNS A INNER JOIN SYSTYPES B 
5 ON (A.XTYPE=B.XTYPE) LEFT JOIN SYS.extended_properties C ON (A.ID=C.MAJOR_ID and A.COLID=C.MINOR_ID)
6  WHERE A.ID= OBJECT_ID('TABLENAME')  ORDER BY A.COLID

1.查询出当前数据库的所有主键信息。

 

 1 SELECT A.parent_obj AS TABLEID,
 2        UPPER(E.NAME) AS TABLENAME,
 3        UPPER(A.NAME) AS INDEXNAME,
 4        UPPER(D.NAME) AS COLNAME,
 5        C.KEYNO AS COLNO,
 6        (SELECT TOP 1 KEYNO
 7           FROM sysindexkeys
 8          WHERE ID = B.ID
 9            AND INDID = B.INDID
10          ORDER BY KEYNO DESC) AS KEYCNT
11   FROM sysobjects   A,
12        sysindexes   B,
13        sysindexkeys C,
14        syscolumns   D,
15        sysobjects   E
16  WHERE (A.xtype = 'PK')
17    AND (A.parent_obj = B.ID AND A.NAME = B.NAME)
18    AND (B.ID = C.ID AND B.INDID = C.INDID)
19    AND (C.ID = D.ID AND C.COLID = D.COLID)
20    AND (A.parent_obj = E.ID AND E.XTYPE = 'U' AND E.NAME <> 'dtproperties')
21  ORDER BY A.parent_obj, A.NAME 

 

 2.查询出当前数据库的所有索引名称及索引字段 ,不包含主键。

 

 1 SELECT X.*, Y.FIELDCNT
 2   FROM (SELECT A.id as tableid,
 3                object_name(A.id) as tablename,
 4                A.name AS INDNAME,
 5                B.INDID,
 6                C.COLID,
 7                C.NAME AS COLNAME
 8           FROM sysindexes A, sysindexkeys B, syscolumns C, sysobjects D
 9          where (A.indid > 0 and A.indid < 255 and (A.status &64) = 0)
10            AND (A.ID = B.ID AND A.INDID = B.INDID)
11            AND (B.ID = C.ID AND B.COLID = C.COLID)
12            AND (C.ID = D.ID AND D.XTYPE = 'U' AND D.PARENT_OBJ = 0 AND
13                D.NAME <> 'dtproperties')
14            AND NOT EXISTS (SELECT 1
15                   FROM sysobjects
16                  WHERE XTYPE = 'PK'
17                    AND PARENT_OBJ > 0
18                    AND NAME = A.NAME)) X,
19        (SELECT ID, INDID, MAX(KEYNO) AS FIELDCNT
20           FROM sysindexkeys
21          GROUP BY ID, INDID) Y
22  WHERE X.tableid = Y.ID
23    AND X.INDID = Y.INDID
24  ORDER BY X.TABLEID, X.INDNAME, X.COLID

3.查询外键，约束，字段默认值。

 

 1 select (CASE a.xtype
 2          WHEN 'F' THEN
 3           '外键'
 4          WHEN 'C' THEN
 5           '约束'
 6          WHEN 'D' THEN
 7           '默认值'
 8        END) AS lx,
 9        a.name AS name,
10        b.text
11   from sysobjects a
12   left outer join syscomments b on a.id = b.id
13  where (a.xtype IN ('C', 'F','D'))
14    AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
15    and a.parent_obj = object_id('表名') 

 

4.查询出所有的递增字段

 

 select name, object_name(id) as tablename
2   from syscolumns
3  where COLUMNPROPERTY(id, name, 'IsIdentity') = 1

 

5.查询存储过程

 select (CASE a.xtype
2          WHEN 'p' THEN
3           '存储过程'
4        end) as lx,
5        a.name,
6        b.text
7   from sysobjects a
8   left outer join syscomments b on a.id = b.id
9  where xtype = 'p'

6.查询视图

 

 

 select (CASE a.xtype
2          WHEN 'v' THEN
3           '视图'
4        end) as lx,
5        a.name,
6        b.text
7   from sysobjects a
8   left outer join syscomments b on a.id = b.id
9  where xtype = 'v'

 

 

7.获取表的基本字段属性

 

 SELECT syscolumns.name,
2        systypes.name,
3        syscolumns.isnullable,
4        syscolumns.length
5   FROM syscolumns, systypes
6  WHERE syscolumns.xusertype = systypes.xusertype
7    AND syscolumns.id = object_id('表名')

 

 

 

8.查询字段默认值。

 

 select  a.XTYPE,   OBJECT_NAME(parent_obj) AS TABLENAME,D.NAME AS COLNAME,C.colid, b.TEXT,C.STATUS
2   from sysobjects a , syscomments B, sysconstraints C ,SYSCOLUMNS D 
3  where (a.xtype = 'D' AND OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
4    AND (A.id = B.id)
5    AND (A.ID=C.CONSTID AND A.parent_obj=C.ID AND C.status  = 2069)
6    AND (C.ID=D.ID AND C.COLID=D.COLID)
7    --and a.parent_obj = object_id('表名') 
8 ORDER BY A.parent_obj

 

9. 所有表信息

 

 

 1  SELECT   
 2     table_name=case when a.colorder=1 then d.name else '' end,  
 3     column_order=a.colorder,  
 4     column_name=a.name,  
 5     [IsIdentity]=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,  
 6     [IsPrimaryKey]=case when exists(SELECT 1   
 7                                     FROM sysobjects   
 8                                     where xtype='PK' and name in (  
 9                                                SELECT name   
10                                                FROM sysindexes   
11                                                WHERE indid in(  
12                                                       SELECT indid   
13                                                       FROM sysindexkeys  
14                                                        WHERE id = a.id   
15                                                           AND colid=a.colid)))  
16                              then '√' else '' end,  
17      column_datatype=b.name,  
18      [Length]=a.length,  
19      [PRECISION]=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  
20      [Scale]=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),  
21      [IsNullable]=case when a.isnullable=1 then '√'else '' end,  
22      [Default]=isnull(e.text,'') 
23      --, [Description]=isnull(g.[value],'')  
24 FROM syscolumns a  
25    left join systypes b   
26        on a.xtype=b.xusertype  
27    inner join sysobjects d   
28        on a.id=d.id    
29            and d.xtype='U'   
30            and  d.name<>'dtproperties' 
31    left join syscomments e   
32        on a.cdefault=e.id  
33    --left join sysproperties g   
34    --    on a.id=g.id   
35    --        and a.colid=g.smallid    
36 order by   
37     a.id,  
38     a.colorder