SQLServer常⽤语句
1. sqlserver查看实例级别的信息，使⽤SERVERPROPERTY函数
select SERVERPROPERTY ('propertyname')
2. 查看实例级别的某个参数XX的配置
select * 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对应figurations.minimum是10、figurations.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、5. 全局系统视图、单个数据库系统视图
sys.database_files --每个存储在数据库本⾝中的数据库⽂件在表中占⽤⼀⾏。这是⼀个基于每个数据库的视图。sys.master_files --master 数据库中的每个⽂件对应⼀⾏。这是⼀个系统范围视图。--sys.database_files、sys.master_files这种的视图，6. ⼀些只存在msdb的系统表，⽽⾮系统视图
dbo.backupsetdbo.log_storehistorydbo.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_who
sp_who2
sp_who2 active (可选参数LoginName, 或active代表活动会话数)
CPUTime (进程占⽤的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后⼀次调⽤存储过程或者执⾏查询的时间)
ProgramName (⽤来初始化连接的应⽤程序名称，或者主机名)
10. 查看某个存储过程的内容
sp_helptext pro_name
11.显⽰某个线程号发送到sqlserver数据库的最后⼀个语句
DBCC INPUTBUFFER
12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句
DBCC INPUTBUFFER (249)
13. 查看某个数据库中是否存在活动事务，有活动事务就⼀定会写⽇志
DBCC OPENTRAN (dbname)
14. 监视⽇志空间
DBCC SQLPERF (LOGSPACE)
15. 查⽆法重⽤⽇志中的空间的原因(⽇志⽆法截断导致⽇志⽂件越来越⼤，但是可⽤空间很⼩，⽆法收缩)
select name,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. 查看数据库各种设置
select name,State,user_access,is_read_only,recovery_model from sys.databases
20. 查看某个数据库中是否存在会话
select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')
21. 查询当前阻塞的所有请求
select * from sys.sysprocesses where blocked>0或source_type,db_source_database_id),t1.resource_associated_entity_quest_quest_session_id,t2.blocking_session_id,t2.wait_duration_ms
22. 查看哪些表被锁了，以及这些表被哪个进程锁了
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC
23. 查询某个job是否被堵塞
select * from msdb.dbo.sysjobs where name='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 1 1FROM sys.sysprocessesWHERE program_name = 'SQLAgent - Generic Refresher')SELECT 'Running'ELSESELECT 'Not Running'
25. 查看活动线程执⾏的sql语句，并⽣成批量杀掉的语句
select '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/ 26. 查看备份进度
SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_ti 27. 查看恢复进度
SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_ti 28. 查看数据库的最近备份信息
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type备注：D 表⽰全备份，i 表⽰差异备份，L 表⽰⽇志备份
29. 查看数据库的历史备份记录，并⽣成restore语句
SELECTCONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,bs.database_name,bs.backup_start_date,bs.backup_finish_piration_date,peWHEN 'D' THEN 'Database'WHEN 'L' THEN 'Log'END
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;
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 up_database_up_databa 32. 查看mirror镜像信息
SELECTdb_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instanceFROM sys.database_mirroring
33. 查询SSRS Report Subscriptions相关的job
SELECTb.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 ON CONVERT(SYSNAME,a.
34. 查看某个数据库的数据⽂件信息，就算是mirror从库的数据⽂件也可以查到，filestream⽬录也可以查到
SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'D
BA');
35. 查看某个数据⽂件信息
select b.pe_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_wth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like '%DTSWonda_1%'
36. 查询实例的数据⽂件总⼤⼩
SELECT sum(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_pe_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%' 39. 查询实例上的每个数据库的⼤⼩
SELECTDB_NAME(db.database_id) DatabaseName,(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
40. 查询总耗CPU最多的前3个SQL，且最近5天出现过
SELECT TOP 3total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运⾏次数],qs.total_worker_ution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后⼀次执⾏时间],max_worker_time /
41. 查询平均耗CPU最多的前3个SQL，且最近5⼩时出现过
SELECT TOP 3total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运⾏次数],qs.total_worker_ution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后⼀次执⾏时间],min_worker_time /
42. 查看当前最耗资源的10个SQL及其spid
SELECT TOP 10session_id,request_id,start_time AS '开始时间',status AS '状态',command AS '命令', AS 'sql语句', DB_NAME(database_id) AS '数据库名',blocking_session_id AS '正在阻塞其他会话的会话ID',wait_type AS
43. 查询某个存储过程被哪些job调⽤了
SELECT *FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )ON STP .job_id = JOB .job_idWHERE STP mand LIKE N'%sp_name%'--以上要查询某个job被哪个job调⽤了，把sp_nam 44. 命令执⾏某个job
EXECUTE msdb.dbo.sp_start_job N'job_name'
45. 查询某表标识列的列名
SELECT COLUMN_NAME FROM lumns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1
sqlserver备份表语句46. 获取标识列的种⼦值
SELECT IDENT_SEED ('表名')
47. 获取标识列的递增量
SELECT IDENT_INCR('表名')
48. 获取指定表中最后⽣成的标识值
SELECT IDENT_CURRENT('表名')
49. 重新设置标识种⼦值为XX
DBCC CHECKIDENT (表名, RESEED, XX)
50. 升级前，查询服务器名、实例名、版本号
select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version
51. ⽤户被grant这样操作赋予的权限
use dbname exec sp_helprotect @username = 'username'
52. 授予某个⽤户执⾏某个数据库的sp的权限
use dbname grant execute to "username"
53. always on
-查看集各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数
SELECT * FROM sys.dm_hadr_cluster_members;
-查看集各节点的信息，包含节点成员的名称，节点成员上的sql实例名称
select * from sys.dm_hadr_instance_node_map
-查看WSFC(windows server故障转移集)的信息，包含集名称，仲裁类型，仲裁状态
SELECT * FROM SYS.dm_hadr_cluster;
-查看AG名称
select * from sys.dm_hadr_name_id_map
-
查看集各节点的⼦⽹信息，包含节点成员的名称，⼦⽹段，⼦⽹掩码
SELECT * FROM sys.dm_hadr_cluster_networks;
-查看侦听ip
select * from sys.availability_group_listeners;
-查看主从各节点的状态
select d.is_local,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 up_database_ -查看辅助副本(传说中的从库)延迟多少M⽇志量
select db_name(database_id),log_send_queue_size/1024 delay_M,* from sys.dm_hadr_database_replica_states where is_primary_replica=0;
plica_server_name, db_name(drs.database_id),uncation_lsn, drs.log_send_queue_size, do_queue_size from sys.dm_hadr_database_replica_states drs join sys.availability_replicas ar plica_plica_id
plica_server_name, db_name(drs.database_id),uncation_lsn, drs.log_send_queue_size,drs.log_send_rate, do_queue_do_rate from sys.dm_hadr_database_replica_states drs join sys.availability_replicas ar 54. 查询实例的FILESTREAM 使⽤的DIRECTORY_NAME
SELECT SERVERPROPERTY('FilestreamShareName')
55. 查询FILETABLE表的数据库对应的DIRECTORY_NAME
select db_name(database_id),* from sys.database_filestream_options
仅仅使⽤filestream功能时，数据库不需要对应的DIRECTORY_NAME
56. 查询FILETABLE表对应的DIRECTORY_NAME
select object_name(object_id),* from sys.filetables
57. 查询filetable表testdb.dbo.table1中的⽂件完整路径名称
SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1
58. 查询所有job的状态是否running
SELECT sj.Name, CASE WHEN sja.start_execution_date IS NULL THEN 'Not running' WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running' WHEN sja.start_execution_date 59. 锁表的四种⽤法
TABLOCKX SELECT * FROM table WITH (TABLOCKX)
查询过程中，其他会话⽆法查询、更新此表，直到查询过程结束
TABLOCK SELECT * FROM table WITH (TABLOCK)
查询过程中，其他会话可以查询，但是⽆法更新此表，直到查询过程结束
HOLDLOCK SELECT * FROM table WITH (HOLDLOCK)
查询过程中，其他会话可以查询，但是⽆法更新此表，直到查询过程结束
NOLOCK SELECT * FROM table WITH (NOLOCK)
查询过程中，其他会话可以查询、更新此表
60. 查询某个发布XX，发布的数据库对象的2种⽅法
发布数据库上执⾏(数据来源这三张表distribution.dbo.MSpublications、distribution.dbo.MSarticles、sysarticlecolumns)
select a.article,a.source_object,a.destination_lid from (select article,article_id,source_object,destination_object from [distribution].[dbo].MSarticles where publication_id in ( select publication_id from [distribution].[dbo].MSpublications 订阅数据库上执⾏
select distinct article from MSreplication_objects where publication='XX'
61. 查询发布信息，发布名称，发布名称对应的发布序号
Select * from distribution.dbo.MSpublications
62. 查询发布名⾥⾯的发布对象的信息，包含表、视图、存储过程等
Select * from distribution.dbo.MSarticles
63. 监控发布订阅是否有异常，执⾏以下5条语句即可
select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(
64. 查询XX表的索引信息
SELECT a.name index_name,c.name table_name,d.name column_name FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id= lid 65. ⽣成sql语句的执⾏计划(select XXX为例，当然select XXX也可以换成执⾏存储过程⽐如exec pro_XXX，都是只⽣成执⾏计划，不产⽣结果集，不会执⾏存储过程)
SET SHOWPLAN_ALL ON; GO select XXX GO SET SHOWPLAN_ALL OFF; GO 或 SET SHOWPLAN_XML ON; GO select XXX GO SET SHOWPLAN_XML OFF; GO
66. 查询名称为XXX的job的最后⼀次运⾏成功的时间
SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) FROM msdb.dbo.sysjobhistory jo
bhis inner join msdb.dbo.sysjobs jobs 67. 查询某张分区表的总⾏数和⼤⼩，⽐如表为crm.EmailLog
exec sp_spaceused 'crm.EmailLog';
68. 查询某张分区表的信息，每个分区有多少⾏，⽐如表为crm.EmailLog
select convert(varchar(50), ps.name ) as partition_scheme, p.partition_number, convert(varchar(10), ds2.name ) as filegroup, convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, ws, 9) as rows from sys.indexes i join sys.partitio 69. 查询分区函数
select * from sys.partition_functions
70. 查看分区架构
select * from sys.partition_schemes
71. 查询ssis包的信息
select * from msdb.dbo.sysssispackages
72. 查询某张表⾥的索引的⼤⼩，如下⽰例表为dbo.table1
SELECT i.name AS IndexName, SUM(page_count * 8) AS IndexSizeKB FROM sys.dm_db_index_physical_stats( db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s JOIN sys.indexes AS i ON s.[object_id] = i.[obje 73. 重建表上的所有索引
alter index all on table_name rebuild with (online=on)
重建表上的某个索引
alter index index_name on table_name rebuild with (online=on)
重新组织表上的所有索引
alter index all on table_name reorganize
重新组织表上的某个索引
alter index index_name on table_name reorganize
74. 查看数据⽂件可收缩空间，结果见Availabesize_MB字段值
select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/10
24 as Usedsize_MB, size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB from sys.master_files 75. 查询某个表中的全部索引的信息
declare @tableName varchar(50) = 'LbaListAlertDetail' declare @tableId int
select @tableId = object_id from sys.objects where name = @tableName
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name ,IX.name AS Index_Name ,IX.type_desc Index_Type ,SUM(PS.[used_page_count]) * 8 IndexSizeKB ,IXUS.user_seeks AS NumOfSeeks ,IXUS.user_scans sqlserver中类似oracle的dba_source的视图是sys.sql_modules
76. 查询某个数据库下的表数据占⽤磁盘容量最⼤的10张表
use XX if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u') drop table #tabName go create table #tabName( table_name varchar(100), rowsNum varchar(100), reserved_size varchar(100),
select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size from #tabName ORDE
R BY size desc
或 select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts from ( SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, al_pages) * 77. 查询某个数据库中是否有create index '+name+ CHAR(10)
select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),definition from '+name+'.sys.sql_modulesWHERE objectproperty(OBJECT_ID, ''IsProcedure'') = 1 AND definition like ''%online%=%on%'' and definition like ''%ind 78. 根据id号查询某个数据库名
SELECT DB_NAME(18)
根据id号查询某个对象名
SELECT OBJECT_NAME(1769220894)
79. 查看收缩的进度100%，此语句要到指定的数据库下执⾏
SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completi
on_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_ti 80. 查看重新组织索引的100%进度
SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_ti 81. 查看存储过程的执⾏计划
SELECT d.object_id , DB_NAME(d.database_id) DBName , OBJECT_NAME(object_id, database_id) 'SPName' , d.cached_time , d.last_execution_time , d.total_elapsed_time/1000000 AS total_elapsed_time, 82. 查看当前⽤户
select system_user
83. 查询ddl修改操作的记录
-执⾏如下到trace⽂件的⽬录和名称
select * aces
-使⽤sqlserver profiler⼯具打开trace⽂件，就可以查到相关记录
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论