sqlserver2022查询语句（sqlserver常用语句大全）

1. sqlserver查看实例级别的信息，使用SERVERPROPERTY函数

selectSERVERPROPERTY ( propertyname )

2. 查看实例级别的某个参数XX的配置

select * from sys.configurations wherename= 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对应

sys.configurations.minimum是10、
sys.configurations.maximum是60，如果sp_configure recovery interval , 75设置为75，超过了这个10--60规范，但是要让75生效，则必须加上WITH OVERRIDE。

4. sqlserver没有系统表可以查询所有数据库下面对象，以下只能在当前数据库下面查

select * from sys.all_objects --查询当前数据库的所有架构范围的对象select * from sys.sysobjects --查询当前数据库的所有对象--sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下面都有select * from sys.databases --在当前数据库下可以查询到所有数据库信息，包含是否on状态select * from sys.sysdatabases --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除--sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下面都有sys.processes --没有这个视图select * from sys.sysprocesses --在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的版本中删除

5. 全局系统视图、单个数据库系统视图

sys.database_files--每个存储在数据库本身中的数据库文件在表中占用一行。这是一个基于每个数据库的视图。sys.master_files--master 数据库中的每个文件对应一行。这是一个系统范围视图。--sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下面都有

6. 一些只存在msdb的系统表，而非系统视图

dbo.backupsetdbo.log_shipping_secondarydbo.restorehistorydbo.sysjobsdbo.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_whosp_who2sp_who2active (可选参数LoginName, 或active代表活动会话数) CPUTime(进程占用的总CPU时间) DiskIO(进程对磁盘读的总次数) LastBatch(客户最后一次调用存储过程或者执行查询的时间) ProgramName(用来初始化连接的应用程序名称，或者主机名)

10. 查看某个存储过程的内容

sp_helptext pro_name

11.显示某个线程号发送到sqlserver数据库的最后一个语句

DBCC INPUTBUFFER

12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句

DBCCINPUTBUFFER (249)

13. 查看某个数据库中是否存在活动事务，有活动事务就一定会写日志

DBCCOPENTRAN (dbname)

14. 监视日志空间

DBCCSQLPERF (LOGSPACE)

15. 查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大，但是可用空间很小，无法收缩)

selectname,log_reuse_wait_desc from sys.databases

16. 查看虚拟日志文件信息

DBCC LOGINFO

结果有多少行，代表有多少虚拟日志文件，活动的虚拟日志文件的状态(status)为2

17. 修复msdb数据库，比如ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修复

dbcc checkdb(msdb);

18. 在您当前连接到的 SQL Server 数据库中生成一个手动检查点

CHECKPOINT[ checkpoint_duration ]--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制

19. 查看数据库各种设置

selectname,State,user_access,is_read_only,recovery_model from sys.databases

20. 查看某个数据库中是否存在会话

selectDB_NAME(dbid),* from sys.sysprocesses where dbid=db_id( dbname )

21. 查询当前阻塞的所有请求

select * from sys.sysprocesses where blocked>0或SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode,t1.request_session_id,t2.blocking_session_id,t2.wait_duration_msFROM sys.dm_tran_locks as t1INNERJOIN sys.dm_os_waiting_tasks as t2ON t1.lock_owner_address = t2.resource_address;或select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作from master..sysprocesses a,master..sysprocesses bwhere a.blocked<>0and a.blocked= b.spid或SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,( CASEWHEN er.statement_end_offset = -1THENLEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2ELSEer.statement_end_offsetEND- er.statement_start_offset)/ 2),qt.text,program_name,Hostname,nt_domain,start_timeFROM sys.dm_exec_requests erINNERJOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSSAPPLY sys.dm_exec_sql_text (er.sql_handle) AS qtWHERE session_Id > 50/* Ignore system spids.*/AND sp.blocked>0AND session_Id NOTIN (@@SPID)或SELECT session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource,transaction_idFROM sys.dm_exec_requestsWHEREstatus = N suspended ;--sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息

22. 查看哪些表被锁了，以及这些表被哪个进程锁了

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type= OBJECT ORDERBY request_session_id ASC

23. 查询某个job是否被堵塞

select * from msdb.dbo.sysjobs wherename= jobname select a.program_name,a.* from master..sysprocesses a where a.program_name like %0D1CE57E8AC5% --把靠前个语句查询到的job_id代入第二个语句的program_name

24. 检查SQL Agent是否开启

IF EXISTS (SELECT TOP 11FROM sys.sysprocessesWHERE program_name =  SQLAgent - Generic Refresher )SELECT Running ELSESELECT Not Running 

25. 查看活动线程执行的sql语句,并生成批量杀掉的语句

select KILL  +CAST(a.spid ASNVARCHAR(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/1000as waittime,a.status,Replace(b.text,,) as sqlmessage,cpufrom sys.sysprocesses as a with(nolock)crossapply sys.dm_exec_sql_text(sql_handle) as bwhere a.status<> sleeping AND a.spid<>@@SPID

26. 查看备份进度

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASEWHEN estimated_completion_time < 36000000THEN 0 ELSEEND + 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_REQUESTScrossapply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE Backup% --and database_id=db_id( cardorder )--OR command LIKE  RESTORE% ORDERBY2DESC

27. 查看恢复进度

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASEWHEN estimated_completion_time < 36000000THEN 0 ELSEEND + 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_REQUESTScrossapply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE RESTORE% --and database_id=db_id( cardorder )--OR command LIKE  RESTORE% ORDERBY2DESC

28. 查看数据库的最近备份信息

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUPBY database_name,typeORDERBY database_name,type备注：D 表示全备份，i 表示差异备份，L 表示日志备份

29. 查看数据库的历史备份记录，并生成restore语句

SELECTCONVERT(CHAR(100),SERVERPROPERTY( Servername ))ASServer,bs.database_name,bs.backup_start_date,bs.backup_finish_date,bs.expiration_date,CASE bs.typeWHEN D THEN Database WHEN L THEN Log ENDAS backup_type,bs.backup_size,bmf.logical_device_name,bmf.physical_device_name,bs.name AS backupset_name,bs.description, RESTORE DATABASE [ +bs.database_name+ ] FROM DISK=N   +bmf.physical_device_name+    WITH NORECOVERY; FROM msdb.dbo.backupmediafamily bmfINNERJOIN msdb.dbo.backupset bsON bmf.media_set_id=bs.media_set_idWHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())ORDERBY bs.backup_finish_date

30. 查询XX库从YYYY-MM-DD日期开始的日志备份记录，并生成restore log的语句

SELECT TOP 1000       S.database_name [Database],       CASE [S].[type]             WHEN L THEN N RESTORE LOG   + QUOTENAME(S.database_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 INNERJOIN msdb.dbo.backupset S ON S.media_set_id = F.media_set_id WHERE S.database_name =  XX AND       S.type =  L AND S.backup_start_date >  YYYY-MM-DD ORDERBY S.backup_start_date ASC

31. 查询always on状态是否正常

select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1

32. 查看mirror镜像信息

SELECTdb_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instanceFROM sys.database_mirroring33. 查询SSRS Report Subscriptions相关的jobSELECTb.name AS JobName, e.name, e.path, d.description, a.SubscriptionID, laststatus, eventtype, LastRunTime, date_created, date_modifiedFROMReportServer.dbo.ReportSchedule aJOIN msdb.dbo.sysjobs b ONCONVERT(SYSNAME,a.ScheduleID) = b.nameJOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionIDJOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemidWHEREe.name =  Report Name Goes Here 

34. 查看某个数据库的数据文件信息，就算是mirror从库的数据文件也可以查到，filestream目录也可以查到

SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N DBA );

35. 查看某个数据文件信息

select b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like %DTSWonda_1% 

36. 查询实例的数据文件总大小

SELECTsum(size*8/1024/1024) FROM master.sys.master_files

37. 查询某个目录中数据库使用的总大小

SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like G:DEFAULT.DATA% 

38. 查询某个目录中哪些数据库占用了8G以上容量

SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like G:DEFAULT.DATA% and a.size*8/1024/1024>8

39. 查询实例上的每个数据库的大小

SELECTDB_NAME(db.database_id) DatabaseName,(CAST(mfrows.RowSize ASFLOAT)*8)/1024 RowSizeMB,(CAST(mflog.LogSize ASFLOAT)*8)/1024 LogSizeMB,(CAST(mfstream.StreamSize ASFLOAT)*8)/1024 StreamSizeMB,(CAST(mftext.TextIndexSize ASFLOAT)*8)/1024 TextIndexSizeMBFROM sys.databases dbLEFTJOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHEREtype = 0GROUPBY database_id, type) mfrows ON mfrows.database_id = db.database_idLEFTJOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHEREtype = 1GROUPBY database_id, type) mflog ON mflog.database_id = db.database_idLEFTJOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHEREtype = 2GROUPBY database_id, type) mfstream ON mfstream.database_id = db.database_idLEFTJOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHEREtype = 4GROUPBY database_id, type) mftext ON mftext.database_id = db.database_id40. 查询总耗CPU较多的前3个SQL，且最近5天出现过