find tables referenced in stored procedures

Question

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

From SQL Server itself, the sysdepends table is the only way that I know of, but it is unreliable.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

A Dy Valued Member Points: 65 More actions · Answer 2

I read that if the database is refreshed then it should be fairly accurate. Would you agree with that?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

That is what I do, but I have heard some say that that does not always work either, and I have definitely seen it miss some stuff.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

You should also be aware that none of this works on procedures that use Dynamic SQL.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

You might want to try:

SELECT * FROM sys.sql_modules WHERE Definition LIKE '%tablename%' OR Definition LIKE '%tablename%'

Now this could get to be tedious what with adding the OR clause so as to include all the table names in your DB.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

And this is unreliable also.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]