4

I have used sp_depends first time like this 

sp_depends TestTable

As result I got some stored procedures and views. Now when I look (Ctrl F for TestTable) at script of stored procedures and views I do not see that they would be referring to TestTable.

If not using script is there any other dependency could be there? If yes how?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Pritesh
  • 1,938
  • 7
  • 32
  • 46

2 Answers2

10

sp_depends is prehistoric and unreliable.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

Even better, consider using Red Gate's free SQL Search tool...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    I do not have access on DMV/DMF and no option of installing Red Gate's tool – Pritesh Aug 29 '12 at 12:41
  • @Pritesh: then use sys.sql_modules: http://stackoverflow.com/search?tab=votes&q=user%3a27535%20sys.sql_modules If you can't, you have no other way. And Red Gate's tool is an SSMS plugin, it doesn't go on the server – gbn Aug 29 '12 at 12:42
  • I did select * from sys.sql_modules where definition like '%TestTable%' I got zero records but sp_depends gave me 16 SP and Views – Pritesh Aug 29 '12 at 12:46
  • @Pritesh: sp_depends is wrong. You stated that in your question. It is that simple. I don't care what sp_depends says. I don't use it. – gbn Aug 29 '12 at 12:48
  • 1
    I blogged about the problems here: https://sqlblog.org/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx – Aaron Bertrand Aug 29 '12 at 12:52
  • @AaronBertrand if select * from sys.sql_modules where definition like '%TestTable%' gives 0 records I can assume no DB object is uisng this table??? – Pritesh Aug 29 '12 at 12:55
  • @gbn if select * from sys.sql_modules where definition like '%TestTable%' gives 0 records I can assume no DB object is using this table??? – Pritesh Aug 29 '12 at 12:55
  • @Pritesh not necessarily. Maybe you have encrypted procedures. Maybe you construct the name with dynamic SQL or pass it in a parameter. Maybe there are foreign keys etc. that wouldn't show up in sys.sql_modules. Maybe it's referenced from other databases. Have you investigated the 16 objects returned by sp_depends? Maybe there is some reason they are listed? – Aaron Bertrand Aug 29 '12 at 12:57
  • @Pritesh you could also be on a case sensitive collation - maybe try `WHERE LOWER(definition) LIKE '%testtable%'` – Aaron Bertrand Aug 29 '12 at 12:58
  • @gbn thanks :) In that case sp_depends has a definate bug as it is giving me list of views and sp. – Pritesh Aug 29 '12 at 12:58
  • @Aaron Bertrand same zero records.. even when i open view or sp given as output i do not see table name ... – Pritesh Aug 29 '12 at 13:01
  • 3
    @Pritesh well, the answer is quite simple: sp_depends is broken, stop using it. Heed gbn's advice and read my blog post. – Aaron Bertrand Aug 29 '12 at 13:03
0

I wrote a stored procedure to search all stored procedures in all databases for text and displays the database name, stored procedure name, and an example of how the text is used. You can also specify two words and it will display only the stored procedures using both words. This is good for searching for a table name and the word INSERT for instance.

If you call it without any parameters, it will give you help on how to use it. I also have other database procedure tools in my GitHub.

sp_FindInAllProcs

Russell Hankins
  • 1,196
  • 9
  • 17