Columns used in a stroed procedure 1

[bhepner]

Is there a way to programatically go through the store procedures in a database and determine what columns a stored procedure is using? I have a database which has around 1200 stored procedures and I need to determine what columns are read or updated by the stored procedures. There is always the brute force method but I am hoping there is a better way. Thanks for the help.

Blake Hepner

 

[tlbroadbent]

This query will provide the info you want.

Select o.Name
From syscomments c join sysobjects o on c.id=o.id
Where c.text like '%ColName%'
And o.type='P' Terry

http://members.home.net/tlbroadbent/index.htm

 

[Andel]

What you need is to run "sp_depends" stored procedure. You'll see which columns your sp is using. Example...

SP_DEPENDS yourstoredproc

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

Great idea from Andel. I had forgotten sp_depends. It does have a shortcoming. You can only run it for one SP at a time. That's not fun if you have 1200 SP's. Use this T-SQL code to build a new SP that will list all columns used in all SP's in the current table.

use master
go
Create proc sp_dependsall as

Select SP_Name=substring(o2.name,1,48),
Col_Name = substring(col_name(d.depid, d.depnumber),1,40),
TblOrView = substring((s.name+ '.' + o.name), 1, 40),
o.Type
From sysobjects o Join sysdepends d On o.id = d.depid
Join sysusers s On o.uid = s.uid
Join sysobjects o2 On d.id=o2.id
Where o2.type='p'
And o.type In ('u','v')

Order By 1,2 Terry

http://members.home.net/tlbroadbent/index.htm

 

[bhepner]

Thanks guys. Both solutions work and made my day a little easier.