Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

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+'%'

 

Legacy Comments


Peter DeBetta, Microsoft MVP - S
2006-09-22
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.


Brett
2006-09-26
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


Lee Everest
2006-09-28
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...:)

jezemine
2006-10-03
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

Brett
2006-10-10
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


jezemine
2006-10-30
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'

:)