查询SQLServer2005中某个数据库中的表结构、索引、视图、存储过
- WBOY原創
- 2016-06-07 15:52:011020瀏覽
原地址：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来查看。