CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Yatrix
Yatrix

Reputation: 13775

How can I find stored procedure calls?

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.

Upvotes: 8

Views: 28225

Answers (4)

HuBeZa
HuBeZa

Reputation: 4761

View the Dependencies of a Stored Procedure:

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

Upvotes: 1

Morphed
Morphed

Reputation: 3619

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+'%'

Upvotes: 6

Taryn
Taryn

Reputation: 247700

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.

Upvotes: 7

marc_s
marc_s

Reputation: 754488

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.

enter image description here

enter image description here

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??

Upvotes: 13

Related Questions