原地址：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=1THEN D.NAMEELSE ” END,
表說明 = CASE WHEN A.COLORDER=1THEN ISNULL(F.VALUE,”)ELSE ” END,
欄位序號 = A.COLORDER,
欄位名 = A.NAME,
標識 = CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,‘ISIDENTITY’)=1THEN ‘√’ELSE” END,
主鍵 = CASE WHEN EXISTS(SELECT 1FROMSYSOBJECTS WHERE XTYPE=‘PK’AND PARENT_OBJ=A.IDAND NAME IN (
SELECT NAMEFROM SYSINDEXES WHEREINDIDIN(
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=1THEN ‘√’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_idAND A.COLID=G.minor_id
LEFT JOIN
sys.extended_properties F
ON
D.ID=F.major_idAND F.minor_id=0
ORDER BY
A.ID,A.COLORDER
PS：如若有的数据库中不存在dbo.sysproperties表，那么只需要将与之相关的注释掉即可。
最简单的查询用户表的SQL如下：
select * from sys.tables
（2）查询SQLServer2005中某个数据库中的索引
SELECT TOP 100PERCENT –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.idIS 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.idIS 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.nameAS 对象名,
o.crdateAS 创建时间,
o.refdateAS 更改时间,
c.textAS 声明语句
FROM dbo.sysobjects o
LEFT OUTERJOIN
dbo.syscomments c ON o.id = c.id
WHERE (o.xtypeIN (‘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来查看。
转载请注明原文链接：查询SQLServer2005中某个数据库中的表结构、索引、视图、存储过
