Site Sections
Posts
83
Comments
600
Trackbacks
40
<< Stored Procedure Logging | Home | Hierarchies with a twist...rocks, no salt >>
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+'%'

 

posted on Friday, September 22, 2006 12:32 PM Print
Comments
# re: How do I find all the tables referenced by Stored Procedures or Functions
Peter DeBetta, Microsoft MVP - S
9/22/2006 2:08 PM
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.

# re: How do I find all the tables referenced by Stored Procedures or Functions
Brett
9/26/2006 11:00 AM
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

# re: How do I find all the tables referenced by Stored Procedures or Functions
Lee Everest
9/28/2006 12:10 PM
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...:)
# re: How do I find all the tables referenced by Stored Procedures or Functions
jezemine
10/3/2006 10:58 PM
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
# False Positives
Brett
10/10/2006 1:45 PM
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

# re: How do I find all the tables referenced by Stored Procedures or Functions
jezemine
10/30/2006 3:20 PM
why? don't tell me you don't love this:

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

:)
Comments have been closed on this topic.
Article Categories
Archives
Post Categories
Image Galleries
Misc
SQL Server