Sql server 2005 query to list of procedures using particular table
Using SQL, can I get list of all stored procedures that are using a particular table say Bank table
This is mainly used at the time when there is alteration in the structure of the bank table
simply executing this would give you list of all sps, views, etc which depend on that particular table
i use this all the time as i work with a db which has over 400 tables
SELECT
o.name
FROM
sys.sql_modules sm
INNER JOIN sys.objects o ON
o.object_id = sm.object_id
WHERE
sm.definition LIKE '%bank%'
this will all the procedures refering the bank.
This is mainly used at the time of when there is alteration in the structure of the bank table
and need to change all the procedures refereing this tableSELECT
o.name
FROM
sys.sql_modules sm
INNER JOIN sys.objects o ON
o.object_id = sm.object_id
WHERE
sm.definition LIKE '%table_name%'
this is the query for displaying the list of procuedures having particular string
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%deleted=0*%' OR definition LIKE '%*deleted=0*%'
this is list out the procedures with the string having deleted=0 in all the procedures, which will help us
to search the string, when there is need to change the procedures based on the test. and conditions used.
This will display the list of procedures