查询SQLServer2005中某个数据库中的表结构、索引、视图、存储过程、触发器以及自定义函数

原地址：http://www.360doc.com/content/10/0926/15/3393452_56532327.shtml

（1）查询SQLServer2005中某个数据库中的表结构

SELECT

表名      = CASE WHEN A.COLORDER=1 THEN D.NAME ELSE '' END,

表說明    = CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,'') ELSE '' END,

欄位序號  = A.COLORDER,

欄位名    = A.NAME,

標識      = CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY')=1 THEN '√'ELSE '' END,

主鍵      = CASE WHEN EXISTS(SELECT 1 FROMSYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (

SELECT NAME FROM SYSINDEXES WHEREINDID IN(

SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN '√' ELSE '' END,

類型      = B.NAME,

佔用位元組數= A.LENGTH,

長度      = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),

小數位數  = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),

允許空    = CASE WHEN A.ISNULLABLE=1 THEN '√'ELSE '' END,

預設值    = ISNULL(E.TEXT,''),

欄位說明  = ISNULL(G.[VALUE],'')

FROM

SYSCOLUMNS A

LEFT JOIN

SYSTYPES B

ON

A.XUSERTYPE=B.XUSERTYPE

INNER JOIN

SYSOBJECTS D

ON

A.ID=D.ID  AND D.XTYPE='U' AND  D.NAME<>'DTPROPERTIES'

LEFT JOIN

SYSCOMMENTS E

ON

A.CDEFAULT=E.ID

LEFT JOIN

sys.extended_properties G

ON

A.ID=G.major_id AND A.COLID=G.minor_id 

LEFT JOIN

sys.extended_properties F

ON

D.ID=F.major_id AND F.minor_id=0

ORDER BY

A.ID,A.COLORDER

 

PS：如若有的数据库中不存在dbo.sysproperties表，那么只需要将与之相关的注释掉即可。

最简单的查询用户表的SQL如下：

 

select * from sys.tables

 

（2）查询SQLServer2005中某个数据库中的索引

 

SELECT TOP 100 PERCENT --a.id,

       CASE

            WHENb.keyno = 1 THEN c.name

            ELSE''

       END               AS表名,

       CASE

            WHENb.keyno = 1 THEN a.name

            ELSE''

       END               AS索引名称,

       d.name           AS 列名,

       b.keyno           AS 索引顺序,

       CASE INDEXKEY_PROPERTY(c.id, b.indid, b.keyno, 'isdescending')

            WHEN1 THEN '降序'

            WHEN0 THEN '升序'

       END               AS排序,

       CASE

            WHENp.id IS NULL THEN ''

            ELSE'√'

       END               AS主键,

       CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')

            WHEN1 THEN '√'

            WHEN0 THEN ''

       END               AS聚集,

       CASE INDEXPROPERTY(c.id, a.name, 'IsUnique')

            WHEN1 THEN '√'

            WHEN0 THEN ''

       END               AS唯一,

       CASE

            WHENe.id IS NULL THEN ''

            ELSE'√'

       END               AS唯一约束,

       a.OrigFillFactor  AS 填充因子,

       c.crdate          AS 创建时间,

       c.refdate         AS 更改时间

FROM   dbo.sysindexes a

       INNER JOIN dbo.sysindexkeysb

            ON  a.id = b.id

            ANDa.indid = b.indid

       INNER JOIN dbo.syscolumns d

            ON  b.id = d.id

            ANDb.colid = d.colid

       INNER JOIN dbo.sysobjects c

            ON  a.id = c.id

            ANDc.xtype = 'U'

       LEFT OUTER JOIN dbo.sysobjects e

            ON  e.name = a.name

            ANDe.xtype = 'UQ'

       LEFT OUTER JOIN dbo.sysobjects p

            ON  p.name = a.name

            ANDp.xtype = 'PK'

WHERE  (OBJECTPROPERTY(a.id, N'IsUserTable') = 1)

       AND (OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)

       AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)

ORDER BY

       c.name,

       a.name,

      b.keyno

 

（3）查询SQLServer2005中某个数据库中的主键、外键、约束、视图、存储过程、触发器以及自定义函数

 

SELECT DISTINCT

TOP 100 PERCENT o.xtype,

CASE o.xtype

     WHEN'X'  THEN '扩展存储过程'

     WHEN'TR' THEN '触发器'

     WHEN'PK' THEN '主键'

     WHEN'F'  THEN '外键'

     WHEN'C'  THEN '约束'

     WHEN'V'  THEN '视图'

     WHEN'FN' THEN '函数-标量'

     WHEN'IF' THEN '函数-内嵌'

     WHEN'TF' THEN '函数-表值'

     ELSE'存储过程'

END AS 类型,

o.name AS 对象名,

o.crdateAS 创建时间,

o.refdateAS 更改时间,

c.text AS 声明语句

FROM dbo.sysobjects o

LEFT OUTER JOIN

dbo.syscomments c ON o.id = c.id

WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND

(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)

ORDER BY

CASE o.xtype

WHEN 'X'   THEN '扩展存储过程'

WHEN 'TR'  THEN '触发器'

WHEN 'PK'  THEN '主键'

WHEN 'F'   THEN '外键'

WHEN 'C'   THEN '约束'

WHEN 'V'   THEN '视图'

WHEN 'FN'  THEN '函数-标量'

WHEN 'IF'  THEN '函数-内嵌'

WHEN 'TF'  THEN '函数-表值'

ELSE '存储过程' END DESC

 

（4）查询SQLServer2005中某个数据库中的所有关系图

select * from dbo.sysdiagrams

 

（5）查询SQLServer2005中某个数据库中的所有对象

select * from sys.sysobjects

 

不同的对象用xtype来标识。

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

这些信息可以通过联机丛书->索引->sysobjects->xtype来查看。