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

Question

Hi All,

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.

Answer 1

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.

Answer 2

There are several techniques

I use sys.sql_dependencies and sp_refreshsqlmodule to ensure that the dependency information is up to date before querying the metadata.

Answer 3

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 

Find Sp form database which is related to(using) table XXX