Wednesday, 27 March 2013

SQL Server : List of Stored Procedure from Table


SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND (SC.Text LIKE '%UPDATE%' OR SC.Text LIKE '%INSERT%')
ORDER BY SO.Name



but a different variation can also be tried-

select
SPECIFIC_NAME
from
INFORMATION_SCHEMA.ROUTINES r
where
(r.ROUTINE_DEFINITION like '%UPDATE%' or r.ROUTINE_DEFINITION like '%INSERT%')
order by SPECIFIC_NAME

No comments:

Post a Comment