how to find where a stored procedure is being used

Question

Using SQL Server 2000, is there a way to search for a pattern globally in all trigger procedures?

Where a stored procedure is being called is hiding from me.

This is my first post so be kind.

Answer 1

This will search triggers, procedures, functions and views on SQL Server 2000 (recommend against this approach on newer versions; see this blog post for a much better way):

SELECT o.name
 FROM syscomments AS c
 INNER JOIN sysobjects AS o
 ON c.id = o.id
 WHERE c.text LIKE '%procedurename%';

Some danger here, of course:

1. syscomments will take procedures > 4000 rows and split them up into multiple rows. So there is a slight chance that a large procedure might only mention your search string on a boundary point, and not turn up at all. There is also a chance that such a procedure might show up in the list twice (you can add a GROUP BY to eliminate that).
2. Beware of false positives. Your search string could be included in a comment. Or if you have a stored procedure named GetAuthorSubscriptions and you're looking for %GetAuthors%, it will still show up. Using a case-sensitive search with a COLLATE clause may help, but not necessarily eliminate it.

More information here:

• How do I find a stored procedure containing <text>?

I highly recommend moving off of SQL Server 2000. If not for the 8 billion other benefits, this task is much easier in more modern versions.

Note that your stored procedure might not be getting called from within the database - it could be an ad hoc call from an app, someone's open copy of Management Studio, or even a job. To search jobs you can use:

SELECT 
  job_name = j.name, 
  s.step_name
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s
ON j.job_id = s.job_id
WHERE s.command LIKE '%procedurename%';

Still hasn't turned it up? Run a server-side trace filtering on TextData LIKE '%procedurename%'...

Answer 2

Using SQL Server 2017 Using SQL Server Management Studio

To view the dependencies of a procedure in Object Explorer

• In Object Explorer, connect to an instance of Database Engine and then expand that
• instance.
• Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
• Expand Stored Procedures, right-click the procedure and then click View Dependencies.
• View the list of objects that depend on the procedure.
• View the list of objects on which the procedure depends.
• Click OK.

Using Transact-SQL
you can follow the steps here

for more info check the original article at
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-dependencies-of-a-stored-procedure?view=sql-server-2017