SQLServer常⽤SQL语句（收藏版）
1. sqlrver查看实例级别的信息，使⽤SERVERPROPERTY函数
lect SERVERPROPERTY ('propertyname')
2. 查看实例级别的某个参数XX的配置
lect * figurations where name='XX'
3. 更改实例级别的某个参数XX的值
sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE
sp_configure显⽰或更改当前服务器的全局配置设置。
RECONFIGURE表⽰SQL Server不⽤重新启动就⽴即⽣效 。
使⽤sp_configure更改设置时，请使⽤RECONFIGURE语句使更改⽴即⽣效，否则更改将在SQL Server重新启动后⽣效。
RECONFIGURE后⾯加WITH OVERRIDE表⽰不管这个值是不是符合要求都会⽣效，⽐如recovery interval的范围值是10--60对应
效，则必须加上WITH OVERRIDE。
4. sqlrver没有系统表可以查询所有数据库下⾯对象，以下只能在当前数据库下⾯查
lect * from sys.all_objects --查询当前数据库的所有架构范围的对象
lect * from sys.sysobjects --查询当前数据库的所有对象
--sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下⾯都有
lect * from sys.databas --在当前数据库下可以查询到所有数据库信息，包含是否on状态
lect * from sys.sysdatabas --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，
这个系统视图会在后续的版本中删除
--sys.databas、sys.sysdatabas这种的视图，在每个数据库的系统视图下⾯都有
sys.process --没有这个视图
lect * from sys.sysprocess --在当前数据库下可以查询所有正在SQL Server 实例上运⾏的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的5. 全局系统视图、单个数据库系统视图
sys.databa_files --每个存储在数据库本⾝中的数据库⽂件在表中占⽤⼀⾏。这是⼀个基于每个数据库的视图。
sys.master_files --master 数据库中的每个⽂件对应⼀⾏。这是⼀个系统范围视图。
--sys.databa_files、sys.master_files这种的视图，在每个数据库的系统视图下⾯都有
6. ⼀些只存在msdb的系统表，⽽⾮系统视图
dbo.backupt
dbo.log_shipping_condary
dbo.sysjobs
dbo.sysjobhistory
--这些系统表只存在msdb数据库，使⽤的时候必须加上msdb前缀
7. sp_lock、sp_who、sp_who2、sp_helptext等⼀些系统存储过程存在于每个数据库中
8. 报告有关锁的信息，会显⽰实例⾥⾯的所有数据库的锁信息、堵塞信息
sp_lock
9. 提供有关当前⽤户、 会话和进程的实例中的信息，可以看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很⼤
sp_who
sp_who2
sp_who2 active (可选参数LoginName, 或active代表活动会话数)
CPUTime (进程占⽤的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后⼀次调⽤存储过程或者执⾏查询的时间)
ProgramName (⽤来初始化连接的应⽤程序名称，或者主机名)
10. 查看某个存储过程的内容
sp_helptext pro_name
11.显⽰某个线程号发送到sqlrver数据库的最后⼀个语句
DBCC INPUTBUFFER
12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句
DBCC INPUTBUFFER (249)
13. 查看某个数据库中是否存在活动事务，有活动事务就⼀定会写⽇志
DBCC OPENTRAN (dbname)
14. 监视⽇志空间
DBCC SQLPERF (LOGSPACE)
15. 查找⽆法重⽤⽇志中的空间的原因(⽇志⽆法截断导致⽇志⽂件越来越⼤，但是可⽤空间很⼩，⽆法收缩)
lect name,log_reu_wait_desc from sys.databas
16. 查看虚拟⽇志⽂件信息
DBCC LOGINFO
结果有多少⾏，代表有多少虚拟⽇志⽂件，活动的虚拟⽇志⽂件的状态(status)为2
17. 修复msdb数据库，⽐如ssms页⾯sql rver agent丢失或看不了job view history等功能，说明msdb坏了，需要修复
dbcc checkdb (msdb);
18. 在您当前连接到的 SQL Server 数据库中⽣成⼀个⼿动检查点
CHECKPOINT [ checkpoint_duration ]
--checkpoint_duration表⽰以秒为单位指定⼿动检查点完成所需的时间，⼀般不使⽤这个参数，让数据库⾃⼰控制19. 查看数据库各种设置
lect name,State,ur_access,is_read_only,recovery_model from sys.databas
20. 查看某个数据库中是否存在会话
lect DB_NAME(dbid),* from sys.sysprocess where dbid=db_id('dbname')
21. 查询当前阻塞的所有请求
lect * from sys.sysprocess where blocked>0
或
source_type,db_source_databa_id),t1.resource_associated_entity_quest_mode, t1.request_ssion_id,t2.blocking_ssion_id,t2.wait_duration_ms
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
或
lect A.SPID as 被阻塞进程,a.CMD AS 正在执⾏的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执⾏的操作from master..sysprocess a,master..sysprocess b
where a.blocked<>0 and a.blocked= b.spid
或
SELECT ssion_Id,spid,ecid,DB_NAME (sp.dbid),nt_urname,er.status,wait_type,
表示认真的成语
[Individual Query] =SUBSTRING (qt.text,er.statement_start_offt / 2,
( CASE
WHEN er.statement_end_offt = -1
THENwifi怎么设置不让别人蹭网
LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE
er.statement_end_offt
END
- er.statement_start_offt)
/ 2),
<,program_name,Hostname,nt_domain,start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocess sp ON er.ssion_id = sp.spid贾人食言
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
WHERE ssion_Id > 50 /* Ignore system spids.*/
AND sp.blocked>0 AND ssion_Id NOT IN (@@SPID)
或
SELECT ssion_id ,status ,blocking_ssion_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
-
-sys.dm_exec_requests返回SQL Server 中正在执⾏的每个请求的信息
22. 查看哪些表被锁了，以及这些表被哪个进程锁了
lect request_ssion_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_ssion_id ASC
23. 查询某个job是否被堵塞
lect * from msdb.dbo.sysjobs where name='jobname'
lect a.program_name,a.* from master..sysprocess a where a.program_name like '%0D1CE57E8AC5%'
--把第⼀个语句查询到的job_id代⼊第⼆个语句的program_name
24. 检查SQL Agent是否开启
IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocess
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'
25. 查看活动线程执⾏的sql语句，并⽣成批量杀掉的语句
lect 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name ,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime
,a.status,,'''','''') as sqlmessage,cpu
from sys.sysprocess as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.status<>'sleeping' AND a.spid<>@@SPID
26. 查看备份进度
SELECT DB_NAME(databa_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'Backup%' --and databa_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
27. 查看恢复进度
SELECT DB_NAME(databa_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
新娘父亲婚礼致辞cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'RESTORE%' --and databa_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC
28. 查看数据库的最近备份信息
SELECT databa_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupt GROUP BY databa_name,type ORDER B 备注：D 表⽰全备份，i 表⽰差异备份，L 表⽰⽇志备份
29. 查看数据库的历史备份记录，并⽣成restore语句
SELECT
CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,
bs.databa_name,
bs.backup_start_date,五行属水的行业
bs.backup_finish_date,
pe
WHEN 'D' THEN 'Databa'
WHEN 'L' THEN 'Log'
END AS backup_type,
bs.backup_size,
bmf.logical_device_name,
所有水果名字bmf.physical_device_name,
bs.name AS backupt_name,
bs.description,
'RESTORE DATABASE ['+bs.databa_name+'] FROM DISK=N'''
+bmf.physical_device_name+ '''WITH NORECOVERY;'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupt bs
dia_t_dia_t_id
WHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())
ORDER BY bs.backup_finish_date
30. 查询XX库从YYYY-MM-DD⽇期开始的⽇志备份记录，并⽣成restore log的语句
SELECT TOP 1000
S.databa_name [Databa],
CASE [S].[type]
WHEN 'L'
THEN N'RESTORE LOG ' + QUOTENAME(S.databa_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'
END [LogRestore],
F.physical_device_name,
S.[Type],
S.backup_start_date,
S.backup_finish_date
FROM msdb.dbo.backupmediafamily F
INNER JOIN msdb.dbo.backupt S
dia_t_id = F.media_t_id
WHERE S.databa_name = 'XX' AND
31. 查询always on状态是否正常大明朝
lect dc.databa_name, d.synchronization_health_desc, d.synchronization_state_desc, d.databa_state_desc from sys.dm_hadr_databa_replica_st 32. 查看mirror镜像信息
