sql数据库文件组有什么（一文总结MSSQL数据库中表结构）
sql数据库文件组有什么（一文总结MSSQL数据库中表结构）
概述今天主要介绍一下怎么去查sqlserver数据库中表结构、索引、视图、存储过程、触发器的一些命令，简单记录一下~
1、查询数据库中的所有数据库名
SELECT dbid name filename crdate FROM Master..SysDatabases ORDER BY Name
2、查询某个数据库中所有的表名
SELECT name crdate FROM SysObjects Where XType='U' ORDER BY name;
3、查询数据库中的xx表结构
SELECT (case when a.colorder=1 then d.name else null end) 表名
a.colorder 字段序号 a.name 字段名
(case when COLUMNPROPERTY( a.id a.name 'IsIdentity')=1 then '√'else '' end) 标识
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键 b.name 类型 a.length 占用字节数
COLUMNPROPERTY(a.id a.name 'PRECISION') as 长度
isnull(COLUMNPROPERTY(a.id a.name 'Scale') 0) as 小数位数 (case when a.isnullable=1 then '√'else '' end) 允许空
isnull(e.text '') 默认值 isnull(g.[value] ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=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.class and f.minor_id=0
where b.name is not null
--and d.name='要查询的表' --如果只查询指定表 加上此条件
order by a.id a.colorder
4、查询数据库中的索引
SELECT TOP 100 PERCENT --a.id
CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名
CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称 d.name AS 列名
b.keyno AS 索引顺序 CASE indexkey_property(c.id b.indid b.keyno 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序 CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键 CASE INDEXPROPERTY(c.id a.name 'IsClustered')
WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集 CASE INDEXPROPERTY(c.id
a.name 'IsUNIQUE') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束
a.OrigFillFactor AS 填充因子 c.crdate AS 创建时间 c.refdate AS 更改时间
FROM dbo.sysindexes a INNER JOIN
dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p ON p.name = a.name AND p.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
5、查询数据库中的主键、外键、约束、视图、存储过程、触发器以及自定义函数
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.crdate AS 创建时间 o.refdate AS 更改时间
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
6、查询数据库中的所有对象
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 = 扩展存储过程
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
