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的系统表全部都不见了，原来微软对它进行了升级，把系统表都隐藏了，全部更改为以系统视图的方式提供。