SQLServer 常备实用的SQL查询语句

SQLServer 常备实用的SQL查询语句

查询表字段及类型

SELECT	b.name AS tablename,
		a.name AS columnname,
		c.name AS typename,
		a.max_length AS typelength 
FROM sys.columns a 
INNER JOIN sys.tables b 
ON b.object_id=a.object_id 
INNER JOIN sys.types c 
ON c.system_type_id=a.system_type_id 
WHERE b.name='TableName'
ORDER BY b.name,a.column_id

查询 执行过的SQL语句

SELECT TOP 11 --这里你想显示多少条看你填多少咯
       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 '2015-08-01 00:00:00' AND '2019-09-02 11:00:00' 
ORDER BY
     QS.total_elapsed_time DESC 

查询某个表被哪些存储过程使用到

	SELECT DISTINCT object_name(id) 
	FROM syscomments 
	WHERE id IN (
		SELECT object_id 
		FROM sys.objects 
		WHERE TYPE ='P'
	) AND text LIKE '%TableName%'

查找哪些存储过程对该表做了新增（更新、删除）操作

	SELECT DISTINCT object_name(id) 
	FROM syscomments 
	WHERE id IN (
		SELECT object_id 
		FROM sys.objects 
		WHERE TYPE ='P'
	) AND text LIKE '%insert intoTableName%'

• 这里和上面的 TableName 是你要查询的表名

该计划任务会执行哪些存储过程

	SELECT top 1 * 
	FROM msdb.dbo.sysjobhistory  AS a 
	JOIN msdb.dbo.sysjobs AS b 
	ON a.job_id=b.job_id 	
	WHERE b.name = '你要查询的计划任务名称' AND step_id = 7 AND run_status = 1 
	ORDER BY run_date DESC

存储过程被哪些计划任务调用

	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 '%你要查询的存储过程%'

查询死锁

	-- 查询死锁
	SELECT    
	    request_session_id spid,   
	    OBJECT_NAME(resource_associated_entity_id) tableName    
	FROM    
	    sys.dm_tran_locks   
	WHERE    
	    resource_type='OBJECT' 
	--复制代码 spid
	 --杀死死锁进程
	KILL 354 

查询触发器

SELECT 	triggers.name AS [触发器],
		tables.name AS [表名],
		triggers.is_disabled AS [是否禁用],
		triggers.is_instead_of_trigger AS [触发器类型],
		CASE WHEN triggers.is_instead_of_trigger = 1 THEN 'INSTEAD OF'
			WHEN triggers.is_instead_of_trigger = 0 THEN 'AFTER'
		ELSE NULL
		END AS [触发器类型描述]
FROM sys.triggers triggers
INNER JOIN sys.tables tables 
ON triggers.parent_id = tables.object_id
WHERE triggers.type ='TR'
ORDER BY triggers.create_date