Cacher is the code snippet organizer for pro developers

-- how to view all procedures (type = 'p')
SELECT * FROM sys.objects
WHERE type = 'P'

-- How to view list of all stored procedures within a database
SELECT * from DatabaseName.information_schema.routines 
WHERE routine_type = 'PROCEDURE'

-- How to view code used to create stored procedure. Syntax is sp_helptext ProcedureName
sp_helptext lsp_my_stored_procedure

-- Alternate way to view code used to create stored procedure. 
SELECT * from DatabaseName.information_schema.routines 
WHERE routine_type = 'PROCEDURE' -- in results, scroll to the right and find the ROUTINE DEFINITION cell, copy that text and paste into a text editor to see code that created the procedure.

-- how to see the last time a stored procedure has been run. Look for Last_Execution_Time in results table.
SELECT  a.execution_count ,
    OBJECT_NAME(objectid) Name,
    query_text = SUBSTRING( 
    b.text, 
    a.statement_start_offset/2, 
    (	CASE WHEN a.statement_end_offset = -1 
    	THEN len(convert(nvarchar(max), b.text)) * 2 
    	ELSE a.statement_end_offset 
    	END - a.statement_start_offset)/2
    ) ,
    b.dbid ,
    dbname = db_name(b.dbid) ,
    b.objectid ,
    a.creation_time,
    a.last_execution_time,
    a.*
FROM    		sys.dm_exec_query_stats a 
CROSS APPLY 	sys.dm_exec_sql_text(a.sql_handle) as b 
WHERE OBJECT_NAME(objectid) = 'YOURPROCEDURENAME'
ORDER BY a.last_execution_time DESC