Easiest way to find the tables in a stored procedure?

[SteveMe]

Any help would be greatly appreciated.

 

[vongrunt]

Find the tables = tables used in stored procedure?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

Use the sysdepends table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

sp_depends is not a 100% (or even 50% accurate ;-( )
For example you create a proc and a table,the proc uses the table
you script this out run the create proc before the create table and in this case no row will be added to the sysdepends table and you will be out of luck ;-(

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

And if sproc calls another sproc or UDF or view... then we have nice hierarchical dependency.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

here is what I mean
First run this

use pubs
Go
create procedure abcdefg
as
select * from abcTable
go

create table abcTable(id int)
go

exec sp_depends 'abcTable'  --  ;-(
exec sp_depends 'abcdefg'  --  ;-(

drop table abcTable
drop procedure abcdefg
Go

And now this

create table abcTable(id int)
go

create procedure abcdefg
as
select * from abcTable
go

exec sp_depends 'abcTable'  --  ;-)
exec sp_depends 'abcdefg'   --  ;-)



drop table abcTable
drop procedure abcdefg

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

take a look at this

s
create procedure abcdefg
as
select * from abcTable
go

create table abcTable(id int)
go

exec sp_depends 'abcTable'  --  ;-(
exec sp_depends 'abcdefg'   --  ;-(
go

nothing right

-- But wait, here is help ;-)

alter procedure abcdefg
as
select * from abcTable
go

exec sp_depends 'abcTable'  --  ;-)
exec sp_depends 'abcdefg'   --  ;-)

drop procedure abcdefg
drop table abcTable

so script out all the procs (make sure that there are no drop statements generated)
change create to alter and run sp_depends for the object

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

first part should have been this

create procedure abcdefg
as
select * from abcTable
go

instead of this

s
create procedure abcdefg
as
select * from abcTable
go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/