Find tables used in stored procedures

[Corporal Agarn]

Hello, I am looking for a way to find what tables are used within a stored procedure. I have around 300 stored procedures that I would like to know what if any tables are used. I can script the stored procedures to a file that can be investigated. Thank you for any leads djj

[Lost User]

djj55 wrote:

I have around 300 stored procedures that I would like to know what if any tables are used.

If it's Sql2005, then you could use the sp_depends[^] procedure to list all the dependencies. It's replaced in Sql2008 with something new, but it would probably still work. Hope this helps :)

I are Troll :suss:

[Corporal Agarn]

Thank you for the reply. As I am currently working with 2000 sp_depends is the ticket. The problem is getting the output into a table. Thank you again, djj

[Lost User]

You're welcome :) There are no 'real' options to get the multiple result-tables into a temptable under Sql 2000 that I'm aware of. In Sql2005, you could try using a CLR sproc. As a hack, perhaps you could execute a DOS-command from SQL? If you can execute isql.exe, then you can pipe the result of the command to a textfile. It would get real messy if you try to parse the resulting textfile using SQL, so it might be easier to execute a small console application that parses it and writes it to the database.

I are Troll :suss:

[Corporal Agarn]

Thank you, I will look into the "DOS" solution tomorrow. Sometime in the next three months we are upgrading to 2008.