查询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 .

原地址：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来查看。