Stored Procedure

Show all current running Stored Procedures

Posted on Updated on

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName

Advertisements

Find a String in a Stored Procedure

Posted on Updated on

If you every decided to do something ridiculous and whatever you did requires that  all SP needs to be updated, here is how you can thumb through the SP in search of the string.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%AS400%’
AND ROUTINE_TYPE=‘PROCEDURE’