x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How do I find all the tables referenced by Stored Procedures or Functions

Like this

SELECT o.name, t.TABLE_NAME, c.text
  FROM syscomments c
  JOIN sysobjects o
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%'+t.TABLE_NAME+'%'

 

Print | posted on Friday, September 22, 2006 12:32 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How do I find all the tables referenced by Stored Procedures or Functions

Actually, that isn't entirely reliable if the code of the procedure is longer than 8000 characters. The text field of syscomments is a varchar(8000) field, so when the code is more than 8000 characters, it chunks it into multiple pieces. If the name of a table happens to go across this boundry, this query will not find it.

For example, the procedure may contain a section that looks like this:

...FROM Customers INNER JOIN...

But when split up in syscomments's text column, it might look like this

row text
--- -------------
1 ...FROM Custo
2 mers INNER JOIN...

And so the query wouldn't find the Customers table because neither text field has the entire name.

A better way to do this is to combine two rows of text from syscomments such that your results search across the combined rows, one row at a time. In other words, you'd check against row 1 and 2, then against 2 and 3, then against 3 and 4, and so on. This way, you can avoid issues with splits across rows.

9/22/2006 2:08 PM | Peter DeBetta, Microsoft MVP - S
Gravatar

# re: How do I find all the tables referenced by Stored Procedures or Functions

Peter,

Thanks for reading and thanks for the insight. And of course that makes sense. I wonder how you would enumerate the 8,000 byte chucks to they can me jammed together I wonder.

I'll have to play with it.

Thanks again

Brett

9/26/2006 11:00 AM | Brett
Gravatar

# re: How do I find all the tables referenced by Stored Procedures or Functions

Ok so I was playing with this today and couldnt find the problem that Peter mentions, although I've run into it before. I found a long procedure (1300 lines) and ran:

select distinct text, colid
from syscomments
where id=object_id ('procname')
order by colid

and got all of the text. Did you make sure and Tools>>options in isqlw, and go to third tab "Results" and change the max chars per col to 8192? Either way it's not a good way to get the syscomments. Do an sp_helptext on sp_helptext and you can see that they handle all of the char(10)/char(13) combinations to get it formatted...:)
9/28/2006 12:10 PM | Lee Everest
Gravatar

# re: How do I find all the tables referenced by Stored Procedures or Functions

One other issue si that this can give you false positives if you have table names in comments in the sproc/udf that are not actually referenced by the sproc/udf
10/3/2006 10:58 PM | jezemine
Gravatar

# False Positives

Well that is certainly true...especially if you work on a thrid part vendor piece of cr*p that is using reserved words as table names...man I hate those guys

10/10/2006 1:45 PM | Brett
Gravatar

# re: How do I find all the tables referenced by Stored Procedures or Functions

why? don't tell me you don't love this:

select [select] from [select * from] where [where]='where'

:)
10/30/2006 3:20 PM | jezemine
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET