Query the contents of stored procedures on SQL Server

sql-server sql-server-2005 tsql relational-database
Ben Aston picture Ben Aston · Oct 7, 2011 · Viewed 43k times · Source

I am exploring a legacy database system and have very little knowledge of its internals. I would like to find all the stored procedures that invoke another stored procedure A.

How best to do this?

Can I write something like this pseudocode:

select name from AllStoredProcedures as Asp where Asp.TextualContent contains 'A'

Asp.TextualContent means the actual SQL contained in the SP.

Answer

Martin Smith picture Martin Smith · Oct 7, 2011 
SELECT OBJECT_NAME(object_id),
       definition
FROM sys.sql_modules
WHERE objectproperty(object_id,'IsProcedure') = 1
  AND definition    like '%Foo%'

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More