SQL SERVER 系统表应用

 SQL SERVER 系统表应用

 

 

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

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

 

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

SELECT A.parent_obj AS TABLEID,
       UPPER(E.NAME) AS TABLENAME,
       UPPER(A.NAME) AS INDEXNAME,
       UPPER(D.NAME) AS COLNAME,
       C.KEYNO AS COLNO,
       (SELECT TOP 1 KEYNO
          FROM sysindexkeys
         WHERE ID = B.ID
           AND INDID = B.INDID
         ORDER BY KEYNO DESC) AS KEYCNT
  FROM sysobjects   A,
       sysindexes   B,
       sysindexkeys C,
       syscolumns   D,
       sysobjects   E
 WHERE (A.xtype = 'PK')
   AND (A.parent_obj = B.ID AND A.NAME = B.NAME)
   AND (B.ID = C.ID AND B.INDID = C.INDID)
   AND (C.ID = D.ID AND C.COLID = D.COLID)
   AND (A.parent_obj = E.ID AND E.XTYPE = 'U' AND E.NAME <> 'dtproperties')
 ORDER BY A.parent_obj, A.NAME 

 2.查询出当前数据库的所有索引名称及索引字段 ,不包含主键。
SELECT X.*, Y.FIELDCNT
  FROM (SELECT A.id as tableid,
               object_name(A.id) as tablename,
               A.name AS INDNAME,
               B.INDID,
               C.COLID,
               C.NAME AS COLNAME
          FROM sysindexes A, sysindexkeys B, syscolumns C, sysobjects D
         where (A.indid > 0 and A.indid < 255 and (A.status &64) = 0)
           AND (A.ID = B.ID AND A.INDID = B.INDID)
           AND (B.ID = C.ID AND B.COLID = C.COLID)
           AND (C.ID = D.ID AND D.XTYPE = 'U' AND D.PARENT_OBJ = 0 AND
               D.NAME <> 'dtproperties')
           AND NOT EXISTS (SELECT 1
                  FROM sysobjects
                 WHERE XTYPE = 'PK'
                   AND PARENT_OBJ > 0
                   AND NAME = A.NAME)) X,
       (SELECT ID, INDID, MAX(KEYNO) AS FIELDCNT
          FROM sysindexkeys
         GROUP BY ID, INDID) Y
 WHERE X.tableid = Y.ID
   AND X.INDID = Y.INDID
 ORDER BY X.TABLEID, X.INDNAME, X.COLID

 

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

select (CASE a.xtype
         WHEN 'F' THEN
          '外键'
         WHEN 'C' THEN
          '约束'
         WHEN 'D' THEN
          '默认值'
       END) AS lx,
       a.name AS name,
       b.text
  from sysobjects a
  left outer join syscomments b on a.id = b.id
 where (a.xtype IN ('C', 'F','D'))
   AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
   and a.parent_obj = object_id('表名')

 

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

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

 

5.查询存储过程

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

 

6.查询视图
select (CASE a.xtype
         WHEN 'v' THEN
          '视图'
       end) as lx,
       a.name,
       b.text
  from sysobjects a
  left outer join syscomments b on a.id = b.id
 where xtype = 'v'

 

 

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

SELECT syscolumns.name,
       systypes.name,
       syscolumns.isnullable,
       syscolumns.length
  FROM syscolumns, systypes
 WHERE syscolumns.xusertype = systypes.xusertype
   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
  from sysobjects a , syscomments B, sysconstraints C ,SYSCOLUMNS D
 where (a.xtype = 'D' AND OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
   AND (A.id = B.id)
   AND (A.ID=C.CONSTID AND A.parent_obj=C.ID AND C.status  = 2069)
   AND (C.ID=D.ID AND C.COLID=D.COLID)
   --and a.parent_obj = object_id('表名')
ORDER BY A.parent_obj

 

 今天，发现Sql server 2005的系统表全部都不见了，原来微软对它进行了升级，把系统表都隐藏了，全部更改为以系统视图的方式提供。