1.查询数据库中表涉及到的存储过程
SELECT A.name
FROM SYSOBJECTS A ,SYSCOMMENTS B
WHERE A.id=B.id and type='p'
AND B.text LIKE '%表名%'
2.查询表索引
SELECT 索引名称=a.name
,表名=c.name
,索引字段名=d.name
,索引字段位置=d.colid
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 AND b.colid=d.colid WHERE a.indid NOT IN(0,255)
and c.name='表名'
3.根据字段列名查询涉及到的表名
SELECT A.NAME TABLE_NAME,
B.NAME COLUMN_NAME
FROM SYSOBJECTS A, SYSCOLUMNS B
WHERE A.ID=B.ID AND B.NAME='列名' AND A.TYPE='U'
4.查询存储过程被哪个定时任务引用
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.command LIKE '%SP名称%’
5.查询库中的所有表
SELECT * FROM sysobjects WHERE xtype='U'
6.查询表中的所有字段
select * from syscolumns WHERE id=object_id('表名);
7.根据字段名称，查询主表
SELECT B.name FROM syscolumns A ,sysobjects B
WHERE A.id=B.id AND A.name='字段名称'
8.根据表名查询涉及的存储过程
select distinct b.name
from dbo.syscomments a, dbo.sysobjects b
where a.id=b.id and b.xtype='p' and a.text like '%表名%'
order by name
9.查询数据库SQL执行记录
SELECT TOP 1000
ST.text AS '执行的SQL语句',
QS.execution_count AS '执行次数',
QS.total_elapsed_time AS '耗时',
QS.total_logical_reads AS '逻辑读取次数',
QS.total_logical_writes AS '逻辑写入次数',
QS.total_physical_reads AS '物理读取次数',
QS.creation_time AS '执行时间' ,
QS.*
FROM sys.dm_exec_query_stats QS
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE QS.creation_time BETWEEN '2020-06-01 00:00:00' AND '2020-06-10 11:00:00'
ORDER BY QS.creation_time desc;
10.SQL Server简洁查询正在运行的进程SQL
--------1.
SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];
---------2.
SELECT TOP 100 t.hostname,
t.loginame,
percent_complete,
[session_id] ,
der.[request_id] ,
[start_time] AS '开始时间' ,
der.[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 '返回结果行数',
t.hostname,
t.loginame
FROM sys.[dm_exec_requests] AS der
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
left JOIN sys.sysprocesses t ON t.spid=der.session_id
WHERE [session_id] > 50
-- AND DB_NAME(der.[database_id]) = 'gposdb'
ORDER BY [cpu_time] DESC
11.查询死锁
select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
exec sp_who 'active'--查看系统内所有的活动进程 BLK不为0的为死锁
exec sp_lock 69 --返回某个进程对资源的锁定情况
SELECT object_name(311672158)--返回对象ID对应的对象名
DBCC INPUTBUFFER (69)--显示从客户端发送到服务器的最后一个语句
