SQL Server 数据库部分常用语句小结

1. 查询某存储过程的访问情况

SELECT TOP 1000 db_name(d.database_id) as DBName, s.name as 存储名字, s.type_desc as 存储类型, d.cached_time as SP添加到缓存的时间, d.last_execution_time AS 上次执行SP的时间, d.last_elapsed_time as [上次执行SP所用的时间(微秒)], d.total_elapsed_time [完成此SP的执行所用的总时间(微秒)], d.total_elapsed_time/d.execution_count AS [平均执行时间(微秒)], d.execution_count as 自上次编译以来所执行的次数 FROM sys.procedures s INNER JOIN sys.dm_exec_procedure_stats d ON s.object_id = d.object_id where s.name="存储过程的名称"  ----请在此处替换要统计的SP ORDER BY d.total_elapsed_time/d.execution_count DESC


2. 查询包含关键字(字符串)的存储过程 SP

Select distinct o.* from sysobjects o, syscomments s where o.xtype="P" and s.id=o.id and text like "%关键字%" order by o.name


3. 查询包含关键字(字符串)的Job 

Select * from msdb.dbo.sysjobs where job_id in(Select job_id from msdb.dbo.sysjobsteps where command like "%XXXXXX%" )


4. 查询哪些SQL语句占用CPU的频率高

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 WHERE [session_id]>50 AND DB_NAME(der.[database_id])="数据库名字"  ----请在此处输入要统计的数据库名字ORDER BY [cpu_time] DESC


5. DB的单用户模式更新成多用户模式alter database 数据库名字 set multi_user;


6. 查询某表的列的详情select * from syscolumns where id=object_id("要统计的表") ---请替换


7. 查询当前SQL Server日志信息 

Exec xp_readerrorlog 0

其实 xp_readerrorlog 一共有7个参数

(1)   存档编号;

(2)  日志类型(1为SQL Server日志,2为SQL Agent日志);

(3)  查询包含的字符串;

(4)  查询包含的字符串;

(5)  LogDate开始时间;

(6)  结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc");

(7) 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc") 。


8. 查询Job 明细

SELECT b.[name] [JobName] ,b.enabled [Enabled] ,a.step_id [StepID] ,b.description [JobDescription] ,a.step_name [StepName] ,a.command [Script] FROM msdb.dbo.sysjobsteps a INNER JOIN msdb.dbo.sysjobs b ON a.job_id=b.job_id