鲁达 概述
今天主要介绍一下怎么去查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;PRECISION') as 长度, isnull(COLUMNPROPERTY;Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空, isnull;') 默认值,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 g on a.id=g.major_id AND a.colid=g.minor_id left join 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, 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, a.name, 'IsClustered') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY, 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, N'IsUserTable') = 1) AND (OBJECTPROPERTY, N'IsMSShipped') = 0) AND (INDEXPROPERTY, 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 IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND (OBJECTPROPERTY, 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 不同的对象用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方面的内容，感兴趣的朋友可以关注下~