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