欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-23 17:32:34
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_t ......

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