Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to find stored procedures where a particular column is not updated.

As an example

SP1:

BEGIN
      UPDATE YourTable
      SET    Foo = @Foo,
             Bar = @Bar
      WHERE  Id = @Id
  END 

SP2:

 BEGIN
      UPDATE YourTable
      SET    Foo = @Foo                 
      WHERE  Id = @Id
  END 

So I want to get all the SP's where Bar is not updated in above example it should return SP2.

Note: one SP can have multiple update statements. Is there any possibility to get the table name of that update statement ?

share|improve this question
1  
I would suggest RedGate SQL Search: stackoverflow.com/questions/11228886/… –  Shawn Melton Apr 4 '13 at 12:23
add comment

2 Answers 2

Try this:

SELECT DISTINCT OBJECT_NAME([object_id])
FROM sys.sql_dependencies AS d
WHERE referenced_major_id = OBJECT_ID('dbo.YourTable')
AND is_updated = 1
AND NOT EXISTS 
(
  SELECT 1 FROM sys.sql_dependencies AS id
   INNER JOIN sys.columns AS c
   ON id.referenced_major_id = c.[object_id]
   AND id.referenced_minor_id = c.column_id
   WHERE id.object_id = d.object_id
   AND id.referenced_major_id = d.referenced_major_id
   AND c.name = 'bar'
);

Caveats:

  1. You didn't mention your version of SQL Server. I'm assuming you are on 2005+.
  2. I'm not sure how reliable the is_updated column is. I've seen major inconsistencies with the is_selected column.
  3. This does depend on your stored procedure being created after the objects and not being subject to deferred name resolution. You may want to recompile all of your stored procedures before relying on the output.
  4. For a lot more about how dependencies can't be relied upon, see http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

Parsing is a real pain in the rear because all of your statements could have slight variations. You may be able to search for patterns like:

SELECT name FROM sys.procedures
  WHERE LOWER(OBJECT_DEFINITION([object_id]))
        LIKE '%update%yourtable%'
  AND LOWER(OBJECT_DEFINITION([object_id]))
      NOT LIKE N'%bar%=%';

But this has all kinds of ways to go astray - some of these patterns could appear in comments, false positives could come because you have an update to yourtable2, you could miss a row because an update references the column bar2, etc. etc.

I wrote a pretty comprehensive search procedure here, but it will only be as reliable as the search pattern you define:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx

share|improve this answer
    
your first query works fine except below scenario: When Store procedure have multiple update statements and one statement uses column while other do not. in that case your query doesn't show this SP as output. any work around for the same ? –  Piyush Patel Apr 5 '13 at 8:26
    
@PiyushPatel it's very early here, but off the top of my head, you'd have to run two queries, one with the NOT EXISTS, and one when you change it to EXISTS, and then manually inspect any that show up on the right but not the left. I'll think about this some more later (after coffee). –  Aaron Bertrand Apr 5 '13 at 10:42
    
@Aron, anything that you have thought of to resolve above issue ? –  Piyush Patel Apr 8 '13 at 3:48
    
@Piyush Other than manually inspecting the ones that have bar referenced in a different UPDATE statement? Sorry, I know of no magic that will do that. –  Aaron Bertrand Apr 8 '13 at 9:50
add comment

You can find the code for your stored procedures in sys.sql_modules. You can write a query on them like this:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.sql_modules 
WHERE [definition] LIKE '%UPDATE YourTable%'
  AND [definition] NOT LIKE '%Bar = @Bar%'

Be warned that this assumes that your code looks EXACTLY like this. So for example if your code looks like this:

UPDATE
    YourTable

It's not going to find it because it doesn't exactly fit the pattern. You can play with the patterns though to get as close as possible to the results you want.

If you are interested I talked about these views in more detail here.

share|improve this answer
    
in some cases 'LIKE '%UPDATE YourTable%' will not work as sometime we pass Alies name instead of the table name. –  Piyush Patel Apr 5 '13 at 7:24
    
Absolutely. This method does have some serious flaws, however if you are working with 2005 instances it's also your best bet. @Aaron's method is better but only works if you are using 2008+ –  Kenneth Fisher Apr 5 '13 at 13:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.