name of tables & views in stored procedure in sql server 2005

Question

I have a stored procedure, I want to know the name of the tables and views use in that stored procedure, can any one suggest how can I do so.

Thanks in advance.

If you build and use dynamic SQL in your stored procedures, the only way to do this is to go in and read it yourself (which, trust me, really sucks). — Philip Kelley, Jun 24 '10 at 13:50

score 5 · Accepted Answer · edited Jun 24 '10 at 13:50

5

You can use sp_depends but this depends on the dependency information being up to date.

Running sp_refreshsqlmodule on all objects in the database can update this if there is any missing dependency information.

edited Jun 24 '10 at 13:50

Cade Roux

answered Jun 24 '10 at 13:48

Martin Smith

Sorry for the edit - I thought this was my answer, and was trying to add a link ;-) – Cade Roux Jun 24 '10 at 13:51
@Cade - Thanks for the edit. Edit Ha Ha Feel free to either leave it in or take it out as you wish! – Martin Smith Jun 24 '10 at 13:53

score 5 · Answer 2 · edited May 23 '17 at 10:27

5

edited May 23 '17 at 10:27

Community

answered Jun 24 '10 at 13:48

Cade Roux

score 1 · Answer 3 · answered Jun 24 '10 at 13:49

1

select
so.name,
sc.text
from
sysobjects so
inner join syscomments sc on so.id = sc.id
where
sc.text like '%ROLES%'-- name of the table

answered Jun 24 '10 at 13:49

Pranay Rana

3 Answers3