IT:AD:SQL Server:HowTo:SQL:DDL:Stored Procs/Retrieving
Summary
When deploying, your scripts may need to investigate the current stored proc to either log it, or compare it against a potentially newer copy.
Process
The code required to retrieve a stored proc is:
SELECT
''''+ 'someStoredProc'+ ''' after update' as comments,
so.object_id,
SUBSTRING(so.name,1,25),
so.create_date, so.modify_date,
sc.colid ,
sc.text
FROM
sys.objects so
INNER JOIN
syscomments sc ON so.object_id = sc.id
WHERE name = 'someStoredProc'
GO