Friday, April 15, 2011

How to Find the list of Stored Procedures which affect a particular column ?

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 http://stackoverflow.com/questions/283214/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?

From stackoverflow
  • 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.

  • 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
    
  • have you tried this : EXEC sp_depends [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 Products

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

    more info on this is available here

0 comments:

Post a Comment