SQL Server 常用近百条SQL语句（收藏版）

文章来源：https://mp.weixin.qq.com/s/pEXio0MNoi1k0w9XgYECNw

作者：廖学强

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

select SERVERPROPERTY (\'propertyname\'B ) $ V , ()

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

select * from sys.conf! / : - K =igurations where name=\'XX\'

3. 更改实例级别的某Q X u { J 6个参数XX的值

sp_configure \'XX\',\'0\' 
RECONFIGURE WITH OVERRIDE

sp_configure显示或更改当前服务器的全局配置设置。

RECONFIGURE表示SQL Server不用重新启动就立即生效 。

使用sp_configure更改设置时，请使用REC$ a J O $ O R I GONFIGURE语句使更改立即生效，否则更改将在SQL Server重新启动后生效。RECONFIGURR 0 | ^ @ Q c p wE后面加WITH OVERRIDE表 m Z ; Q示不管这个值是不是符合要求都会生效，比如recovery interO c B q Xval的范围值是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 这种的视图，s E y M在每个数据库的系统视图下面都=  y z * v z F @有
select * from sys.databases --在当前数据库下可以查询到所有数据库信息X z c h Z，包含是否on状态
select * from sys.sysdat &  ^  (tabaseM  1 j W Fs --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除
--sys.databases、sys.6 l 9 U 9 b n ?sysdatabases这种的视图，在每个数据库的系统视图下面都有
sys.processes --没有这个视图
select * from sys.sysprocesses --在当前数据库下B  x 9 ; F可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视a 2 a V r q a i图会在后续的版本中删除

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

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

6. 一些只存在msdb的系统表，而非k S a g_shipping_secondary
dbo.restorP : ` xehistory
dbo.sysjobs
dbo.sysjobhistory
--这些系统表只存在msdb数据库，使用的时候必. ( 5须加上msdb前缀

7. sp_lock、sp_who、sp_who2、sp_helpE O x F 2 ptext等一些系统存储过程存在于每个数据库中

8. 报告有关锁的信息，会显示实例里面的所有数据库的锁信息、堵塞信息

sp_lock

9. 提供有关当前用户、 会话和进程的实例中的信息M r / T ) t / * 9，可以B s l o看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transactif 3 I K v + d lon是否很大

sp_who 
sp_who2 
sp_who2 active (可选参数LoginName, 或active代表活动会话数Y l T  N过程的内容
sp_helptext pro_name

11.显示某个线程号发送到sqlservert w U  [ X R R [ 8 [ses

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

DBCC LOGINFO

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

17. 修复msdb数据库，比如% : ? ~ $ 8 x {ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修V q Y复

dbcc checkdb (msdb);

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

CHECKPOINT 
--checkpoint_duraR 0 % _ u F L @tion表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制

19. 查看数据库各种设置

select name,State,user_access,is_read_only,recovS x H ) J } Aery_model from sys.databases

20. 查看某个数据Y B { Y } ;est_session_id,t2.blocking_session_id,t2.waiP o u j f (t_duration_ms
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasksD # ) z + as t2
ON t1.lock_owner_address = t2.resource_addrew ? 5 m / J 1 yss;
或
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的D 2 7 C } r , u x操作
from master..sysB 4 : kprocesses a,master..sy[ u 2 ysprocesses b
where a.blocked<>0 and a.b- ^ t 9 s Tlocked= b.spid
或
SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,
=SUBSTRING (qt.text,er.statement_sV w 3 m @ O c X /tart_offset / 2,
( CASE
WHEN er.statement_end_offset = -1
THo J c `EN
LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE
er.statement_end_offD 5 R z L | y 0set
END
- er.statement_start_offset)
/ 2),
qt.text,prox o Y i 5 ]gram_nJ Z c 6ame,Hostname,nt_domain,start_time
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) Ae r的信息

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

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.d; ( Z . Y +  m_tran_locks w^ ] 4 V U lhere resource_type=\'OBJECT\' ORDERI h ` _ 8 . _ 2 w BY request_; & s &session_id2 r J k [ R ASC

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

select * from msdb.dbo.sysjobs where name=\'jobname\'
select a.program_name,a.* fr? r ( l K s m 6 /om master..sysprocesses a where a.program_name li- ! G B Vke \'%0D1CE57E8AC5%\'
--把第一个语句查询到的job_id代入第二个语句的program_name

24. 检查/ g c # RSQL Agent是否开启

IF EXISTS (
SELECT TOP 1 1
Fw { /ROM sys.sysprocesses
WHERE program_name = \'SQLAgent - Generic Refresher\'
)
SELECT \'RunW D ( V A 9 7 Qning\'
ELSE
SE1 , M ( ( X { nLECT \'Not Running\'

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

select \'KILL \'+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REP3 & d K 1  LACE(hostname,\' \',\Z # 7 g  P c I X g Zame ,replace(program_name,\' \',\'\') as program_name
,REs  e R . T #PLACE(loginame, \' \', \'\') AS loginame, db_name(% A g K x c g ! ea.dbid) AS DBname0 6 + e I 7 y,B _ ; 7 z }spid,blocked3 z ? Q / t g l d,waittime/1000 as waittime
,a.status,Replace(b.text,\'\'\'\',\'\'\'\') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross a% { Spply sys.dm_exec_sql_text(sql_handle) as b
where a.st f `tatus<>p G L;\'sh 8 t * Qleepin6 s 5 5 b 5 d t Ug\' AND a.spid<>@@SPID

26. 查看备份进M ~ D 2 p 9度

SELECT DB s s m jB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN \'0\' ELSE \'\' END + RTRIM(estimated_coe c ] % b # S Ampletion_time/1000/3600)y m : f Q D R  6
+ \':\' + RIGHT(\'0\t t b' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ \':\' + RIGHT(\'0\' + RTRIM((estimated_completion_time/1000)%# d K T ( |60), 2) AS 
,b.text as tsql
,*
FROM SYSz Y C x.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE \'Backup%\' --and database_id=db_id(\'cardorder\')
--OR command LIKE \'RESTORE%\'
ORDER BY 2 DESC

27. 查看恢复进度

SELECT DB_NAME(P m 7database_id) AS Exec_DB
,pr @ ` w m &ercent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN \'0\' ELSE \'\' END + RTRIM! q 5(estimated_completion_time/1000/3600)
+ \':\' + RIGHT(\'0\' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ \':\' + RIGHT(\'0\'E Q ; J 9 = 5 + RTRIM((estimated_completion_time/1000)%60), 2) AS 
,b.text as tsql
,*
FROM SYS.DM_ t % p hEXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_+ f - N i m G ]handle) as b
WHERE command LIKE \'RESTORE%\' --and database_id=db_id(\'cardorder\')
--ORS ]  8 A command LIKE \'RESTORE%\'
ORDEu } v # _ & m TR BY 2 DESC

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

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY databaW 2 -  u fse_name,type ORDER BY database_name,type
备注：D 表示全备份，i 表示差异备份，L 表示日志备份

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

SELECT
CON/ } cVERT(CHAR(100),SERVERPROPERTY(\'Servername\'))A& G | ; t ;S Server,
bs.database_R w W $ N 4 ^ rname,
bs.backup_start_d! f L w 4 T m Wate,
bs.backup_finish_date,
bs.expiration_date,
CASE bs.type
WHEN \'D\' THEN \'Database\'
WHEN \, p b'L\' THEN \'Log\'
END AS backup_type,
bs.backup_siz@ ? ` x D Ae,
bmf.logical_device_name,
bmf.physical_device_name,
bs.name AS backup8 h w R x 0 S |set_name,
bs.description,
\'RESTORE DATABASE  FROM DISK=N\'\'\'
+bmf.physical_device_name+ \'\'\'WITH NORECOVERY;\'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bV u O e B / Gs
ON bmf.media_set_id=bs.media_set_id
WHERE bs.backup_start_date>DATEADu T 1 . e $D(DAY,-y J d f i1,GETDATE())
ORDER BY bs.backup_finish_date

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

SELECT TOP 1000 
      S.databasn t p 0 T R ke_name , 
      CASE . 
            WHEN \'L\' 
            THEN N( 7 8 d n , m m\'RESTORE LOG \' + QUOTENAME(S.database_name) + N\' FROM DISK = \'\'\' + F.physical_device_name + N\'\'\' WITH NORECOVERY;\' 
      END , 
      F.physical_devic/ ] Ke_name, 
      S., 
      S.backup_start_datP + 6 W n ve, 
      S.backuL 5 | 3p_finish_date 
FROM msdb.dbo.backupmediafami+ [ b y R _ ~ly F 
INNER JOIN msdb.dbo.backupset S 
ON S.media_set_id = F.media_set_id 
WHERE S.database_name = \'XX\' AND 
      SK 1 D q ^ W J , P.type = \'L\' AND S.backup_start_date > \'YYYY-MM-DD\' ORDER BY S.backup_start_date ASC

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

select dc.database_name, d.synchronization_healthI Z : Z +_desc, d{ B %.synchronization_state_desc,v  f ! O * s d.database_state_desc from sys.dG 8 K nm_hadr_database_replica_states d join sys.avp Q Q z U H I vailability_da8 : ( 3 K * ) 2 Htabases_cluster dc~ J C B K 3 ) 0 on d.group_database_id=dc.group_database_id and d.is_local=1

32. 查看mirror镜像信息

SELECT
db_name(databasx H R Y de_id),
mirroring_state_desc,
m% a m l T 0 : [irroring_role_desc,
mirroringj n W t T C % 7_partner_name,
mirroring_partner_ins& L l X L V ytance
Fc L E a b ( xROM sys.database_mirroring

33. 查询SSRS Report Subscriptions相关的job
SELECT
b.name AS JobName
, e.name
, e.path
, d.description
, a.SubscriptionID
, laststatusj t : 6
, eventtype
, LastRg A r @ g * M { TunTime
, date_created
, date_modified
FROM
ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.name
JOIN ReportServer.dbo.ReportSchedule c ONw ( } @ 4 V L b.name = CY c X M  ? P H O Kirror从库的数据文件也可以查到，filestream目录也可以查到
SELECT db_name(database_id),* FROM master.sys.masterX / : u j k_files Wg V E ? @ 9 N THERE database_id =DB_ID(N\'DBA\');

35. 查看某个数据文件信息
select b.name,a.type_desc,a.na3 * cme,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases{ j o ; K - J J b on a.database_id=b.database_id and a.physical_name like \'%DTSWonda_1%\'

36. 查询实例的数据文件总大小
SELECT sum(size*8/1024/1024) FROM master.sy6 ( q I J z | is.mast, N y Ter_files

37. 查询某U 9 O { m 3 ; v ?个目录中数据库使用的总大小
SELECT a.size*6 7 # z e V8/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.X W . Z C r G ~type_desc,a.name datafilg ~ ` zena* ] ! * ~ Z c @ dme,a.physical_nn { # 1 f G l 0 )ame FROM master.sys.master_files a join sys.sysd= R natabases b on a.database_id=b.dbid and a.physical_name like \'G:\\DEFAULT.DATA%\' and a.size*8/1024/1024>8

39. 查询H q 实例上的每个数据库的大小
SELECT
DB_NAME(dC l Xb.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 Lo5 * K 1 F K b RgSizeMB,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.Teh Z 5 f { . @ =xtIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(s@ m F Eize) RowSize FROM sys.master_files WHERE type = 0 GROUP BY dataW n g ) qbase_id, type) mfrows ON mfrl W M Sows.database_iZ & n @ Z @ : e xd = db.database_t y 2 W : i v + gid
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.S M o x ^ Ymaster_files WHEREl ; O - type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = d# w W M Q + J + &b.da9 8 H ptabase_id
LEF4 c z N ST JOIN (SELECT database_x H 1idu I ^ / x @ r U, SUM(size) StreamSize FROM sys.master_files WHE~ T . t P e G ,RI 1 7 m 3 0 R }E type = 2 GRl l 8OUP BY database_id, typeQ P { ^ / M % , |) mfstream ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSO q 8 L . G C 1ize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mfl Z I + 1text.datab^ m F I | mase_id = db.database_id

40. 查询总耗CPU最多的前3个SQL，且最近5天出现过
SELECT TOP 3
total_worker_time/1q r Y 7 $ g000 AS ,execution_count ,
qs.total_worker_time/qs.execution_count/1000 AS ,
l& ) S q w % g m wast_execution_time AS ,max_/ 7 n 8 M W @worker_q y Ytime /1000 AS ,
SUBSTT : n | 2RING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS , qt.text ,
qt.dbid, dbname=d@ } 4 $ L 4b_name(qt.dbid),
qt.objectid,objew a  r qct_name(qt.objectid,qt.dT { H O .bid) Obje K 0 = l = NctName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())
ORDEM 6 [ O m  g F BR BY total_worker_time DESC

41. 查询平均耗CPU最多的前3个SQL，且最近5小时出现过
SELECT TOP 3
total_worker_time/1000 AS ,execution_count ,
qs.total_worker_time/qs.eg M $ 5xecution_count/1000 AS ,
last_executip u j von_time AS ,min_worker_time /1000 AS ,
max_worker_time /1000 AS ,
SUBSTR! % | nING(qt.text,qs.statement_start_o* ? y O 6ffset/2+1,
(CASE WHEN qs.stB  ^ | Xatement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE q{ W # F m f 4 cs.statemenq : N f b F R at_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用, qt.text ,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

42. 查看当前最耗资源的10个SQL及其spid
SELECY , e ST TOP 1q U C E h  \'开始时间\',statusu G x X G / 3 AS \'状态\',
command AS \'命令1 n  8 T U \',d_sql.text AS \'sql语句\', DB_NAME(database_id) AS \'数据库名\',
blocking_session_id AS \'正在阻塞其他会话的会话ID\',
wait_type AS \'等待资源类型\',wait_time AA B W } n uS \'等待时间\',wait_r, y f Z k 7 P : :esource AS \'等待的/ 5 . 7 j资源\',
reads AS \'物理读次数\',writes AS \'写次数\',logiW , l (cal_reads AS \'逻辑读次数\',
row_count AS \'返回结果行数X j s H \'
FROM sys.dm_exec_reque! = ] , ysts AS d_request
CROSS APPLY
sys.dm_exec_sql_text(d_request.sql_handle) AS d_sq7 + h w + 1l
WHERE session_id>50
ORDER BY cpu_time DESCr : W K t c H
--前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为backD $ 0ground
43. 查询某个存储过程被哪些job调用了
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 .comman~ * 4 6 cd LIKE N\'%sp_name%\'
--以上要查询某个job被哪个job调用了，把sp_name存储过程名字改成job_c : ( r 5 3 3name作业名字即可

44. 命令执行某个job_ L I
EXECUTE msdb.dbo.sp_start_job N\'job_% 0 i H Tname\'

45. 查询某表标识列的列名
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=\'表名\' AND COLUMNPROPERTY(OBJ| 7 CECT_ID(\'表名\'),COLUMN_NAMEE A : N v ( `,\'IsIde% l d b Tntity\')=1

46. 获取标识列的种子值
SELECT IDENT_SEED (\; T i 2 h N D   y 2\')
47. 获取标识列的递增量
SELECT IDENT_INCR(\'表名\')

48. 获取指定表中最后生成的标识值
SELECT IDENT_CURRENT(\'表名\')

49. 重新设置标识种子值为XX
DBCC CHECKIDENT (表名, RESEED, XX)

50. 升级前，查询服务器名、实例名、版本号 
select SERVERPROPERTY(\'machi: * $nename\'),@@SER7 I . / $ 1 XVERNAME,SERVERPROPERTY (\'editiu ^ ~ C + K * won\'),@@version

51. 用户被grant这样操作赋予的权限 
use dbname 
exec sp_helprotect @usernameE 6 r = \'username\'
52. 授予某= ? B 5个用户执行某个数据库的sp的权限
use dbname 
grant execute to \"username\"

53. always on
-查看j . 集群各节点的信息，包含节点成员的名称，类型，状态，拥有E 7 2 i , z 0的投票仲裁数
SELECT * FROM  sys.dm_hadr_cluster_members;
-查看集群) f b H z P [ = F各节点的信息，包含C w q 2 s c节点成员的K S U # ? /  G 1名称，节点成员上的sql实例名称
select * from sys.dm_hadr_in+ F lstancZ , 4 _ 9 ? 4 He_node_map
-查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态
SE- X : Y 5 m n :LECT * FROM SYS.dm_hadr_cluster;
-查看AG名称
select * fromL 9 = w 5 sys.dm_hadr_name_id_map= h y r i b  * 0
-查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码
SELECT * FROM  sys.dm_hadr_cluster_networkj b [ os;
-查看侦听ip
select * from sys.availability_group_listeners;
-查看主从各节点的状态
select d.is_local,dc.database_name, d.syn} ; 7 Lchronization_health_desc, 
d.syncN f & : s a +hronization_state_desc, d.database_state_desc 
from sys.dm_hadr_dah S 6 u m ;tabase_replicV M z Sa_states d 
join sys.availability_databases_cluster dcy x 5 9 
on d.group_database_id=dc.group_database_id;
-查看辅助副本(传说中的从库)延迟多少M日志量
select db_name(database_id),log_send_queue_size/1024 delay_M,* 
from sys.dm_hadr_database_replica_states wheW b y % : g Mre is_pR o :rimary_replicj Q ` _ ?a=y S 8 d0;
select ar.replica_server_name, db_name(dr 8 3 H } W Ts.database_id),drs.truncation_lsn, 
d3 h G G E jrs.log_send_queue_size, drs.redo_queue_size 
from sys.dm_hadr_database_replica_states drs 
join sys.availability_replicas ar on drs.rep4 } J slica_id=ar.rs S x geplica_id where drs.is_local4 L + E v P X=0;
select ar.replica_server_name, db_name(drs.database_id),drs.truncation_ls= o kn, 
drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,D W  7 H Qdrs.redo_rate 
from sys.dm_hadr_. Q ,database_replica_states drs 
joiU Q F = @ nn sys.avaiY I F i blability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0
--log_send_queue_size 主数据库中尚未发送到辅y  / u 8助数据库的日志记录量 (KB)
--log_send_rate 在最后一个 X W R u (_n~ i   hame(database_id),* from sys.database_filestg 4 ^reA S b |am_options
仅仅使用filestream功能时，数据库不需要对应的DIRECTORY_NZ & P s j d 5 1AME

56. 查询FILETABLE表对应的DIRECTORY_NAME 
select object_name(objecM ! l [ t Q / }t_id),* from sys.fK  t 4iletables

578 ; H I H L. 查询filetable表testdb.dbo.table1中的文件完整路径名称 
SELECT FileTableRootPath()+.Q d } Z 5 } FGetFileNamespacePath(Y $ ] H a C [),name FROM testdb.dbo.table1

58. 查询所有job的状态是否running 
SELECT sj.Name, 
    CASE 
        WHEN sja.start_execution_date IS w | L ! B o q NS NULL THEN \'Not running\' 
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NU; 4 n dLL THEN \'Running\' 
        WHEN sja.start_execa N P m X V : : Dution_date IS NOT NULL AND sja.stop r { & _ Y } S 5p_executI i Qion_date IS NOT NULL THEN \'Not running\' 
    END AS \'RunStatus\' 
FROM msdb.dbo( x B.sysjobs sj 
JOIN msdb.dbo.sysjobactiR U @ Hvity sja 
ON sj.job_iy ` * W n J Td = sja.job_id 
Wv P G B ) K =HERE session_id = ( 
    SELECT MAX(session_id) FE + $ !ROM msdb.dbo.sysjobactivity) order by RunStatus desc;
59. 锁表的四种用法
TABLOCKX 
SELECT * FROM table WITH (TABLOCI  1 # ` 9 s  H，直到查询过程结束 
HOLDLOCK 
SELE^ G  ! y T ] oCT * FROM tabX t u Kle WITH (HOL$ X KDLOCK)
查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束2 v Y ! 9 + ^ W
NOLOCK 
SELECT * FRj J +OM table WITH (NOLOCK)
查询过程中，其他会话可以查询、更新此表

60. 查询某个发布XX，发布的数据库对象的2种方法
发布数据库? d L k M上执行(数据来源这三张表distribution.db` s R 9 X 3o.MSpublica] b L { E Dtions、distribution.dbo.; % a 3 `MSarticles、sysarticlecolumns) 
select a.article,a.source_objectX P a I ; X I w,a.destination_object,b.colid from 
(select article,article_id,source_object,destination_y x { } J a !object 
from ..MSarticles where publication_id in 
( select publication_idD s : S g X c from 
..MSpubu O @ j z G Y P Olications where publication=\'XX\' 
) 
) a 
inner join 
(select * from replicate1.dbo.sysarticlecolumns) b 
on a.article_id=b.artid order by a.article
订阅数据库上执行 
select distinct aK H v u $ R Wrticle  from MSreplication_ob F U P s b L ;jects where publication=\'XX\'
61. 查询发布信息，发布名称，发布名称对应的发布序号 
Select * from distribution.dbo.MSpun ~ * V * d X a +blications

62. 查% o s询发布名里面4 h [ | U f o  n的发布对象的信息@ ? y { E，包含表、视图、存储过程等 
Select * from  distribution.dbo.MSarticles

63. 监控( ? m o L K @发布订阅是否有异常，执行以下5条语句即可
select * from .. WHERE error_id != 0 AND  >= DATEADD(HOF 0 4 bUR, -1, GETDATE()) 
sele~ 3 6 ~ R # }ct * from .. WHERE error_id != 0 AND v % f 4 B _ >= DATEADD(HOUR, -1, GETDATE()) 
select * fre 4 lom .. WHERE error_id !M ` M q a Q  f Y= 0 AND  >=t F a Dy &  UATEADD(HOUR, -1, GETDATE()) 
select * from ..MSrepl_er& U # Orors order by 2 desc
select * from msdb.dbo.sysH Y q nreplicationalei q I } X i Srts order by 7 desc

64. 查询XX表的索引信息 
SELECT a.name index_name,c.nameV ! c table_name,d.name column_name 
F. E V c N 9 R |ROM sysindexes a JOIN sysindexkeys b 
ON a.id=b.iV V i _ P 1 U wd A1 w x 6 dND a.ind, S 7id=b.indid 
JOIN sysobjec+ U W l Vts c 
ON b.id=c.idY W y X : n q 3 p 
JOIN sys; F W j f y g ,columns d 
ON b.id=d.id= AND b.colid=d.colid 
WHERE a.indid NOT IN(0,255) AND c.name in (r 6 3 L Q 3\'XX\')

65. 生成sql语句的执行计划(select XXX为例，当然select XXX也可以换成执行存储过程e l E比如exec pro_XXX，都是只生成执行计划，不产生结果集，不会执行存储过程) 
SET SHOWPLAN_ALL ON; 
GO 
select XXX 
GO 
SS H ^ ^ R = N X FET 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 /V ! Y L 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) 
FROM msdb.dbo.sysjobhistory jobh0 x Gis inner join msdb.dbo.sysjobs  jobs 
on jobhis.joH i 7 ) a v o ^ rb_id = jobs.job_id AND j( = a O H 6 hobhis.step_id = 0 AND jobhis.run_status = 1 
and jobs.name=\'XXX\' 
ORDER BY 1 DESC
67. 查询某张分区表的总行数和大小Y ` . a 9 p i p，比如表为crm.EmailLog 
exec sp_spaceused \'crm.EmailLog\';

68. 查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog 
select convert(varchar(50), ps.name 
) Z Vn sys.data_spaces ds2 on dds.dataY K , R_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partitionp $ - C ; Y B_numbeR  7 ) X ? J &r 
and p.object_id = i.object_id and p.index_id = i.index_id 
join s| U vys.partition_funf  F W _ w ; dctio[ ; ons pf on ps.function_id = pf.function_id 
LEFT JOb N _ s 5IN sys.PartiH d ytion_Range_v5 l L S K k N G galues v on p[ M / # { Pf.function_id = v.function_6 P 7 { lid 
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object5 f R { )  3 f S_id = object_id(\'@ B Y / k [ Vcrm.EmailLog\') 
and i.index_id in (0, 1) 
order by p.partitio= k L } xn_number

69. 查询分区函数 
select * from sys.partition_functions

70. 查看分区架构 
select * from sys.partition_schemes

71. 查询ssis包的信息 
select * from msdb.dbo.sysssispackages

72. 查询某张表里的索引的大小,如下示例表为dbo.table1 
SELa S p @ ?  . 1ECT 
    i.name              AS IndexName, 
    SUM(page_count * 8) AS I= E Y ^ h m P andexSizeK9 N 2 , z bB 
FROM sys.dm_db_ino n * + 6 e } U ,dex_physical_stats( 
    db_id(), object_id(\'dbo.table1\'), NULL, NULL, \'DETAILED\') AS s 
JOIN sys.indexes AS i 
ON s. = i. AND s.index_id = i.index_id 
GROUk N R 1 l v W = oP BY i.name 
ORDER BY i.name

73. 重建表上的所有索引
alter index all on table_name rebuild with (online=on)
重建表上的某个索引
altere 1 X P i = index index_name on table_name rebuild with (online=on)
重新组织表上的所g j _ 6 u ] ; 有索引 
alter index all on table_name reorganize
重f p M f v $ &新组织表上的某个索引 
alter index index_nap T | I X k  mme on tablx l ` (e_name reorganize

74. 查看数据文件可收缩空间，结果见Availabesize_MB字段值 
select nam. n je ,7 W b ( f * Isize*8Z : m/1024 as Totalsize_MB ,CAST(FILEPm C M [ { 7 O R uROPERTY(name,\'SpaceUsed\') AS int)*8/1024 as Usedsize_MB, 
size*8/1024 - CAST(FILEPROPERTY(name, \'SpaceUsed\') AS int)*8/1024 AS Availabesiz0 K ) C ~e_MB 
 from sys.m! c k B $ 0 ) k +aster_files where database_id=db_id(N\'DBNAME\')
75. 查询某- Q { v个表中的全l w Q ` o S B H v部索引的信息 
declare @tableName varchar(50) = \'LbaListAlertG X o ( . ( |Detail\@ Z [ ; ` u' 
declare @tableId int 
select @tableId = object_id 
from sys.objectsp t | L * 
where name = @n G =tableName 
SELECT O- h j ^ ^ s p A tBJ( a X A EECT_NAME(IX.OBJECT_ID) Table_Name 
       ,IX.name AS Index_Name 
       ,IX.type_dG G 9 / 2 Gesc Index_Type. j F | r N F 
       ,SUM(PS.) * 8 IndexSizeKB 
       ,IXUS.user_seeks AS NumOfSeeks 
       ,IXUS.user_scans AS NumOfScans 
       ,IXUS.user_look} S B Zups AS NumOfLookups 
       ,IXUS.user_updates AS NumOfUpdates 
       ,IXUS.last_uk c s ! U ! r }ser_seek AS LastSeek 
       ,IXUS.last_user_scan AS LastScan 
       ,IXUS.last_user_lookup AS LastLookup 
       ,IXUS.last_user_update AS LastUp? Q . - 0date 
FROM sys.in] & L B  $ fdexes IX 
INNER JOIN sys.dm_db_index_M d } _ 7usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.i J c [ & 2OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_paM # 9 + ! [ jrtition_stats PS on PS.object_id=IX.object_id 
WHERE OBJECTPROPERTY(IX.OBJECT_| ) V y V U h CID,\'IsUserTable\') = 1 
 and IX.OBJECT_ID = @tse4 K S z N % % gr_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.use# B . / % 0 n [ Fr_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update
sqlserver中类似oracle的dba_source的视图是sys.sql_modules

76. 查询某个数据库下的表数据占用磁盘容量最大T u h ) 8的10张表 
use XX 
if exists(J a . . -select 1 from te. : B *mpdb..sysobjects wherd y h q ` = 8 U 6e id=object_id(\'tempdb..#tabName\') and xtype=\A e ; E e , s'u\') 
drop table #tabName 
go 
create table #tabName( 
table_nam_ X a Q , l ) 3 ue varchar(100), 
rowsNum varchar(100), 
reserved_size varchar(100), 
data_sizU Y = ae varchar(100), 
index_size v[ V = R 8archar(100), 
unused_size varchZ z d - k G & H bar(100) 
)5 U $ _ 
  
declare @name varchar(K y a a 9 # ? =100) 
declare cur cursor for 
select nam- t ( N # Ye from sysobjects where xtype=\'u\' order by name 
open cur 
fetch n` 2 bext from curK P r 6 j into @name 
while @@fetch_status=0 
begin 
    insert into #tabName 
    exec1 u p N sp_spaceused @name 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 
select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SU* )-2)) size 
from #tabName) Z !  Q ORDER BY sr | S y - h - ~ize desc
或 
select top 10 a w 9.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts 
from ( 
SELECT 
    t.NAME AS T~ 2 ~aj ( J ! , DbleName, 
    s.Name ASg 8 M P M SchemaName, 
    p.rows AS RowCounts, 
    SUM(a.toP 4 c W ( + 5tal_pages) * 8 AS TotalSpaceKB6 i P z 7, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS Unusedd j N = R cSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1O 9 ^ H024.00, 2)H ] T P f p x q ; AS NUMERIC(36, 2)) AS UnusedSpaceMB 
FROM 
    sys.tables t 
INNER JOIN       
    sys.ix  mndexes i ON t.OBJECT_ID = i.object_id 
INNB w D v 9 f / +ER JOIN 
    syS ` O z } ~ @ Os.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON p.par* | 9tition* z | $ #_id = a.container_id 
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = sS p I F.schema_id 
WHERE 
    t.NAME NOT LIKE \'C + C d c ? Mdl P - p R v s 9t%\' 
    AND t.is_ms_shipped = 0 
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.P O N x X 0Rows) a 
GROUP BY  a.tablename,I p 7 ^ y v &a.SCHEMANAME 
order by sum(a.TotalSpace] ; l b : 6MB) desc 
--L | f 这个比上一个专业

 
77. 查询某个数据库中是否有create index \'+name+ CHAR(10) 
set c Zlect \'use \'+name+ C} N ( YHAR(10) +\'sele{ 6 _ct DB_NAME(),OBJECT_NAME(OBJECT_ID),d. * ] 9 4 # 7 yefinition from \'+name+\'.sys.sql_modules
WHERH X % 4 m : R zE objectproperty(OBJECT_ID, \'\'IsProcedure\'\_ x ; { | x * Y T') = 1 
AND definition like \'\'%T h V p / + { 1 #online%=%on%\'\' and definitl N a 2ion like \'\'%indeG J B O J z j Cx%\'\'\) 7 A F ^' from sys.databases;

78. 根据id号查询某个数据库名 
SELECT DB_NAME(18) 
根据id号查询某个对象名 
SELECT OBJECT_NAME(1769220894)
79. 查看收缩的进度100%，此语句要U V t R l 6到指定的数据库下执行
SELECT DB_NAME(database_id) AS Exec_DB
,percenW V O u j f 2 H 6t_complete
,CASE WHEN estimM ~ Q P ? q h / 8ateda w + m  W l z !_completion_time < 36000000
THEN \'0\' ELSE \'\' END + RTRIM(estimated_completion_time/1000/3600)
+U = I X u \':\' + RIGHT(\'0\Q 4 H' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ \':\' + RIGHT(\'0\' + RTRIM((estimated_cf J + , $ompletion_time/1000)%60), 2) AS 
,b.text as tsql
,J I 8 8 L Z a 3*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle)A 3 C ! as b
WHERE comm W , : F Jmand LIKE \'DbB i &ccFilesCompact%\' --and database_id=db_id(\'cardorder\')
ORDER BY 2 DESC

80. 查看重新组织r _ b @ : : J S C索引的100%进度
SELECT DB_| 4 X 8 9 g k p kNAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN \'0\' E^ Q oLSE \'\' END + RTRIM(estimated_completion[ 2 , H T 6_time/1000/3600)
+ \':\' + RIGHT(\'0\' + RTRIM((eR 0 l B - , = I fstimated_completiY X t _ ` Q 9 7 Jon_t/ | S q E lime/1000)%3600/60), 2)
+6 1 / @ e Z \':\' + RIGHT(\'0\' + R8 X cTRIM((estimated_completion_time/1000)%60), 2) AS 
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTW = $ 0 S
cross apply sys.dm_exe1 e X - | 8 d wc_sql_text(sql_handle) as b
WHERE command LIKE \'%REORGANIZEW s j / ~ Y |%\' --and database_id=db_iL X 6 Z ud(\i ^ 9 | M } + r q'cardorder\')
ORDER BY 2 DESC

81. 查看存储过程的执行计划 
SELECT 
        d.object_id , 
        DB_NAME(d.database_id) DBName , 
        OBJY ~  C a H q 8 jECT_NAME(object_id, database_id) \'SPName\' ,; i j 
        d.cached_time , 
        d.last_execution_$ ; W o A [ x )time , 
        d.total/ & T_elapsed_time/1000000    AS total_elL J I 7  @ g 6 capsed_time, 
        d.total_elapsed_time / d.execution_count/1000000 
                                        AS [avg@  S a_elapsed_time] , 
        d.last_elapsed_time/1000000     AS lastC y s R H ` Z_elapsed_time, 
        d.execution_count , 
        d.total_physical_reads , 
        d.last_physical_reads , 
        d.total_logical_writes , 
        d.last_logical_reads , 
        et.text SQLText , 
        eqp.query_plan executionplan 
FROM    sys.dm_exec_procedureG  s R 9 q l_stats AS d 
CROSS APPLY sys.dm_exec_sql_text(d.sql_handl- W p 8 Ie) et 
CROSS APPLY sys.dm_exec_query. m 9 r_plan(d.plan_handle) eqp 
WHERE   OBJECT_NAME(object_id, database_id) = \'xxxx\- i * ) k  +' 
ORDE# % A P h x g } !R BY  DESC;

82. 查看当前用户 
selec^ ! *t system_user

83. 查询ddl修改操作的记录 
-执行如下N V 5找到trace文件的目录和名称 
select * from Sys.traces
-使用sql8 ) Qserver profileu Y o B 8 / 4 s &r工具打开trace文件，就可以查到相关记录

原文链接：http://blog.itpub.net/30126024/viewspace-26385B b r ^ & N23/