10

Im working on this large DB which has a lot of the business knowledge embedded in the SPs[I know!] and there is a lot of chaining between the SPs. i.e one stored proc calling another.

Im want to find out a list of stored procedures which update a particular column. How would I do that.

Using showplan_All as outlined in SQL Table and column Parser for stored procedures doesnt work for me, because this is a shared dev db.

using a Sp from master db scanning system text as described is not feasible because I dont have access to the master db.

So how can I find this informaion?

Improve this question
1

5 Answers 5

Reset to default
17

From system view sys.sql_dependencies you can get dependencies at column level.

DECLARE @Schema SYSNAME DECLARE @Table SYSNAME DECLARE @Column SYSNAME SET @Schema = 'dbo' SET @Table = 'TableName' SET @Column = 'ColumnName' SELECT o.name FROM sys.sql_dependencies AS d INNER JOIN sys.all_objects AS o ON o.object_id = d.object_id INNER JOIN sys.all_objects AS ro ON ro.object_id = d.referenced_major_id INNER JOIN sys.all_columns AS c ON c.object_id = ro.object_id AND c.column_id = d.referenced_minor_id WHERE (SCHEMA_NAME(ro.schema_id)=@Schema) and o.type_desc = 'SQL_STORED_PROCEDURE' and ro.name = @Table and c.name = @Column GROUP BY o.name
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This gives accurate results including * also. Thanks.!!
I have tried this with SET @Schema = 'dbo'; SET @Table = 'Orders'; SET @Column = 'OrderID' in the Northwind database, and it returns no results.
13

Have you tried this : EXEC sp_depends @objname = [table name of the column you are interested in].

So for example, if you had a column named Price in a table named Product, you would execute this: EXEC sp_depends @objname = N'Product'.

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 :-)

sp_depends page on MSDN

Improve this answer

Comments

8

Try something like this:

use YourDatabase; select [Name] from sys.procedures where object_definition([object_id]) like '%YourColumnName%';

Obviously this has the potential to generate a lot of false positives depending on what the column is named but at least you will have a list of procedures to sift through.

Improve this answer

Comments

1

Here's one that works in SQL 2000+; Note that as Andrew noted in his, you will get false positives depending on your column name, but it's a starting place:

SELECT DISTINCT o.Name FROM syscomments c JOIN sysobjects o ON c.ID = o.ID WHERE c.Text LIKE '%ColumnName%' ORDER BY o.Name
Improve this answer

Comments

0 
use msdb go select * from sysjobs j inner join sysjobsteps s on j.job_id=s.job_id where command like '%HBR_INSTRUMENT%'
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.