1、检索关键字：sql server内置存储过程,sql server查看系统信息
2、查看磁盘空间：EXEC master.dbo.xp_fixeddrives ,
--查看各个数据库所在磁盘情况
SELECT DB_NAME(df.database_id) as dbName,
physical_name AS DataFile,
size*8/1024 AS 'FileSize(MB)',
volume_mount_point AS Drive,
CAST(total_bytes/1024/1024/1024 AS VARCHAR) + ' GB' AS DriveSize,
CAST(available_bytes/1024/1024/ 1024 AS VARCHAR) + ' GB' AS SpaceAvailable
FROM sys.master_files df
CROSS APPLY sys.dm_os_volume_stats(df.database_id, df.file_id) ovs
where DB_NAME(df.database_id)='db_tank'
3、修改表名/列名：(1)表：exec sp_rename 'test101','test100' （2）列：exec sp_rename 'test101.name','name1'
4、查看视图/过程/对象内容：exec sp_helptext v_test101（带有格式的）
5、创建文件目录：exec MASTER.dbo.xp_create_subdir 'c:\MSSQL\Data'
6、查看错误日志：exec xp_readerrorlog ，循环错误日志：sp_cycle_errorlog
Exec xp_readerrorlog 0,1,Null,Null,'20130409 12:10','20130409 12:30','Asc'
/*
(1). 存档编号（0~99）
(2). 日志类型（1为SQL Server日志，2为SQL Server Agent日志）
(3). 查询包含的字符串
(4). 查询包含的字符串
(5). LogDate开始时间
(6). LogDate结束时间
(7). 结果排序，按LogDate排序（Desc、Asc）
*/
详细参考：https://blog.csdn.net/wacthamu/article/details/24436629，https://blog.csdn.net/v1t1p9hvbd/article/details/71524155
查看错误信息代码 select * from db_tank.sys.messages where message_id= 15281
查看错误日志物理路径：SELECT SERVERPROPERTY('ErrorLogFileName')
查看错误日志目录与文件大小：exec sys.xp_enumerrorlogs
7、更新统计信息：exec sp_updatestats（所有） ，UPDATE STATISTICS Person.Address WITH FULLSCAN（单表）
8、查看系统进程：
（1）常见DMV
系统进程：select * from sys.sysprocesses
用户请求：select * from sys.dm_exec_requests
会话进程：select * from sys.dm_exec_sessions
等待进程：select * from sys.dm_os_wait_stats
（2）详细语句
--包含批处理中当前运行到的SQL（child_Query）
select status,start_time,command,percent_complete,wait_type,text as parent_Query,
[child_Query] = SUBSTRING(qt.text,r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2
ELSE r.statement_end_offset
END - r.statement_start_offset )
/ 2) ,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt --详细版,查看CPU消耗最多的10个语句
SELECT TOP 10
[cpu_time],
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
ORDER BY [cpu_time] DESC
更具体分析
--看查调用语句与父语句以及来源情况
SELECT spid,
start_time,
[Database] = DB_NAME(sp.dbid) ,
command,
[User] = nt_username ,
[Status] = er.status ,
[Wait] = wait_type ,
[Individual Query] = SUBSTRING(qt.text,
er.statement_start_offset / 2,
( CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
* 2
ELSE er.statement_end_offset
END - er.statement_start_offset )
/ 2) ,
[Parent Query] = qt.text ,
Program = program_name ,
hostname ,
nt_domain ,
[Spid] = session_id ,
blocking_session_id
FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_id > 50 -- Ignore system spids.
AND session_id NOT IN ( @@SPID ) -- Ignore this current statement. ORDER BY 1 --查看所有SQL正在执行的进度详情
SELECT
r.session_id ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
r.start_time,
r.[status],
r.blocking_session_id,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement] ,
r.wait_type,
r.wait_time,
r.wait_resource,
r.cpu_time ,
r.total_elapsed_time / 60000 AS[elapsed_minutes],
r.reads ,
r.writes ,
r.logical_reads,
s.host_name,s.program_name
FROM sys.dm_exec_requests AS r
join sys.dm_exec_sessions s on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY 1 ---SQL Server查询正在执行的SQL语句及执行计划
select ds.session_id,dr.start_time,db_name(dr.database_id),dr.blocking_session_id,ds.host_name,
ds.program_name,ds.host_process_id,ds.login_name,dr.status,
dr.command,dr.wait_type,dr.wait_time,dr.open_transaction_count,
dr.percent_complete,dr.estimated_completion_time,dr.row_count,
SUBSTRING(st.text, (dr.statement_start_offset/2)+1,
((CASE dr.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE dr.statement_end_offset
END - dr.statement_start_offset)/2) + 1) AS statement_text,
st.text as full_text,
qp.query_plan
from sys.dm_exec_sessions ds,sys.dm_exec_requests dr--,sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(dr.plan_handle) as qp
where ds.session_id>50
and dr.session_id<>@@spid
and ds.session_id=dr.session_id
--and dr.sql_handle=qs.sql_handle
and dr.database_id>4 -- 2008R2以下版本 想要看图形界面，直接复制内容，重命名为.sqlplan --查看阻塞与被阻塞语句 SELECT R.session_id AS BlockedSessionID ,
S.session_id AS BlockingSessionID ,
Q1.text AS BlockedSession_TSQL ,
Q2.text AS BlockingSession_TSQL ,
C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,
C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,
S.original_login_name AS BlockingSession_LoginName ,
S.program_name AS BlockingSession_ApplicationName ,
S.host_name AS BlockingSession_HostName
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id
INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id
INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1
CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2
9、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）
select * from [数据库名].[dbo].[sysfiles]，sys.master_files
--查看数据库初始大小，现在大小，初始与现在的大小差
USE db_tank;
WITH cte
AS (
SELECT DB_NAME(database_id) AS name,
mf.name AS db_filename,
mf.physical_name,
CAST((mf.size / 128.0) AS DECIMAL(20, 2)) AS initial_size_MB,
CAST((df.size / 128.0) AS DECIMAL(20, 2)) AS actual_size_MB,
CASE mf.is_percent_growth
WHEN 0 THEN STR(CAST((mf.growth / 128.0) AS DECIMAL(10, 2))) + ' MB'
WHEN 1 THEN STR(mf.growth) + '%'
END AS auto_grow_setting
FROM sys.master_files mf
JOIN sys.database_files df ON mf.name = df.name
WHERE mf.database_id = DB_ID()
)
SELECT *,
actual_size_MB - initial_size_MB AS change_in_MB_since_restart
FROM cte; --------------------- select size/128.0/1024 as size_GB,* from db_tank.[dbo].[sysfiles]
10、转换文件大小单位为MB：（sql server默认单位是kb） *8位KB /1024位MB
select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles
11、查询当前数据库的磁盘使用情况：Exec sp_spaceused
12、查询数据库服务器各数据库日志文件的大小及利用率 ：DBCC SQLPERF(LOGSPACE)
*****查看用户级进程：
select status,start_time,command,percent_complete,wait_type,text,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s
*****查看数据库状态：select * from SYS.DATABASES
查看表结构 ：sp_help table_name 需要把状态切到对应的数据库，不能用数据库.schema.表名的方式
判断查看是否存在：
【1】表
IF OBJECT_ID('db_tank..TS_UnrealContestRankReward') IS NULL
BEGIN
CREATE TABLE
END
【2】存储过程
IF OBJECT_ID('db_tank..TS_UnrealContestRankReward') IS NOT NULL
DROP PROCEDURE UnrealContestRankReward
GO
CREATE PROCEDURE ……
END
【3】--列
IF COL_LENGTH(N'BattleTeam_ActiveRecordInfo',N'ID') IS NULL
BEGIN
END
【4】主键
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE parent_obj=OBJECT_ID('db_tank..ServerActiveState') AND xtype='PK')
BEGIN
END
【5】默认约束
IF COL_LENGTH(N'[dbo].[Pet_TemplateInfo]', N'LowDamage') IS NOT NULL
begin
declare @DF1 varchar(100)
SELECT @DF1=name FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('Pet_TemplateInfo')
AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('Pet_TemplateInfo'), 'LowDamage', 'ColumnId')
exec ('alter table Pet_TemplateInfo drop CONSTRAINT '+@DF1)
ALTER TABLE [dbo].Pet_TemplateInfo DROP COLUMN LowDamage
endalter table consortia drop constraint [DF_Consortia_fightPower]
alter table consortia alter column fightPower bigint not null;
alter table consortia add constraint DF_Consortia_fightPower default(0) for fightPower;
刷新视图
SELECT DISTINCT
'EXEC sp_refreshview ''' + name + ''''
FROM db_tank.sys.objects AS so
WHERE so.type = 'V'
【6】默认约束
alter table Consortia alter column fightpower bigint not null
ALTER TABLE [dbo].Consortia ADD DEFAULT ((0)) FOR fightpower
go
【7】自增列 identity
select * from db_tank.sys.columns
where object_id=object_id('sys_users_detail')
AND is_identity=1
SQL性能查询分析
13.查看执行时间和cpu占用时间
Product为表名
set statistics time on
select * from dbo.Product
set statistics time off
14.查看查询对I/0的操作情况
set statistics io on
select * from dbo.Product
set statistics io off
如果物理读取次数和预读次说比较多，可以使用索引进行优化。
SSMS中设置：查询--->>查询选项--->>高级
被红圈套上的2个选上，去掉sql语句中的set statistics io/time on/off 试试效果。
15、查看日志文件.ldf的详情：use db_name ;DBCC LOGINFO;
16、查看数据库文件与文件组信息
--查看数据库文件与文件组信息
SELECT
name as [database_name],
COUNT (*) AS [DataFiles],
COUNT (DISTINCT data_space_id) AS [Filegroups],
SUM (size)*8/1024 AS [Size(MB)] --default Kb
FROM sys.master_files
WHERE [type_desc] = N'ROWS' -- filter out log files/data_space_id 0
AND [database_id] > 0 -- filter out system databases
AND [FILE_ID] != 65537 -- filter out FILESTREAM
GROUP BY [database_id],name;
GO
17、查看数据库大小
select name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from db_tank.dbo.sysfiles
union all
select name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from db_logs.dbo.sysfiles
18、查看日志记录详细信息：select * from Fn_dblog(null,null)
19、Sql Server 查看存储过程在哪些作业中被调用
过程被作业调用
SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N’% sp_name %’
20、sql 查询某个表在哪些存储过程（SP）中使用
（1）.查询某个表被哪些存储过程（以下简称 SP）使用 :
表被存储过程调用
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%Sys_Users_Detail.IsVIP%'
--sp_depends tab
表依赖关系，表之间关系 （参考：https://www.cnblogs.com/gered/p/10812374.html）
（2）.查找那些过程对该表做了更新操作：
select distinct object_name(id) fromsyscomments where id in
(select object_id from sys.objects where type='P') and text like'%update tablename%'
（3）.查询当前数据库中所有SP：
select name as 存储过程 fromsysobjects where type = 'P
21.查看锁情况 ： select * from sys.dm_tran_locks 注意：db_name(db_id)可以获取数据库名字/数据库名称
22.查看表是否分区及分区信息
已分区的表：
- SELECT DISTINCT
- t.name AS TableName
- ,ps.name AS PSName
- ,fg.name AS FileGroupName
- ,f.name AS [FileName]
- ,f.physical_name AS [FilePhysicalName]
- --,dds.destination_id AS PartitionNumber --去除注释即可显示文件的分区数
- FROM sys.tables AS t
- INNER JOIN sys.indexes AS i
- ON (t.object_id = i.object_id)
- INNER JOIN sys.partition_schemes AS ps
- ON (i.data_space_id = ps.data_space_id)
- INNER JOIN sys.destination_data_spaces AS dds
- ON (ps.data_space_id = dds.partition_scheme_id)
- INNER JOIN sys.filegroups AS fg
- ON dds.data_space_id = fg.data_space_id
- INNER JOIN sys.database_files f
- ON f.data_space_id = fg.data_space_id
- WHERE t.name = 'tableName'
未分区的表：
- SELECT t.[name], i.[name], i.[index_id], f.[name]
- FROM sys.indexes i
- INNER JOIN sys.filegroups f
- ON i.data_space_id = f.data_space_id
- INNER JOIN sys.tables AS t
- ON i.[object_id] = t.[object_id]
- WHERE t.name='tablename'
23、查看慢查询语句
SELECT TOP 10 TEXT AS 'SQL Statement'
,last_execution_time AS 'Last Execution Time'
,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
,execution_count AS "Execution Count"
,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC