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' :) |