How can I find stored procedure calls?

Question

Is there a way I can find where stored procedures are called in a SQL Server 2005 database?

I tried using Find, but that doesn't work like it does in Visual Studios.

Thanks in advance.

Answer 1

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

So in your case, if you know what the stored procedure is called that you're interested in - just key that into the search box and SQL Search will quickly show you all the places where that stored procedure is being called from.

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

Answer 2

You can try using the View Dependencies in SQL Server Management Studio.

Right-click on the stored procedure and select View Dependencies. However I have found it is not always 100% accurate.

Answer 3

You could create a 'find' SP

I use this one to search for the text in database objects:

CREATE sp_grep (@object varchar(255))
as

SELECT distinct
'type' = case type
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end,
o.[name],
watchword = @object
FROM dbo.sysobjects o (NOLOCK)
JOIN dbo.syscomments c (NOLOCK)
ON o.id = c.id
where c.text like '%'+@object+'%' 

Answer 4

View the Dependencies of a Stored Procedure:

select *
from sys.dm_sql_referencing_entities('[SchemaName].[SPName]', 'OBJECT');