By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Finding Stored Procs that Ref/Update a column

P: n/a
Could any suggest to me a good way to programmatically identify which SPs
update a database column. I would like to create a cross reference for our
database.
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"Chad" <ch**************@unisys.com> wrote in message
news:cs**********@trsvr.tr.unisys.com...
Could any suggest to me a good way to programmatically identify which SPs
update a database column. I would like to create a cross reference for our
database.


Check out sp_depends and sysdepends in Books Online. Unfortunately, there is
no entirely accurate way to get this information in MSSQL, because of things
like deferred name resolution (you can create a proc referencing a table
which doesn't exist) and dynamic SQL (the column name may not even be in the
proc). But depending on the size and complexity of your database, sp_depends
might be good enough.

Simon
Jul 23 '05 #2

P: n/a
Thanks for the response.

Sp_depends seems to tell me what SPs reference a table or view.

I am trying to determine which SPs update a COLUMN in a table.

Any suggestions.

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:41********@news.bluewin.ch...

"Chad" <ch**************@unisys.com> wrote in message
news:cs**********@trsvr.tr.unisys.com...
Could any suggest to me a good way to programmatically identify which SPs
update a database column. I would like to create a cross reference for
our database.


Check out sp_depends and sysdepends in Books Online. Unfortunately, there
is no entirely accurate way to get this information in MSSQL, because of
things like deferred name resolution (you can create a proc referencing a
table which doesn't exist) and dynamic SQL (the column name may not even
be in the proc). But depending on the size and complexity of your
database, sp_depends might be good enough.

Simon

Jul 23 '05 #3

P: n/a
If you know the tables that the proc references then you can scroll
thru the tables to find if you see a match on the columns...

Loop thru each table thats been referenced...

If exists (Your column name) IN (select name from syscolumns where id =
object_id('TheTableName'))
Print "Bingo!!!!.. Found column"
Else
Pring "Try harder"

Jul 23 '05 #4

P: n/a

"Chad" <ch**************@unisys.com> wrote in message
news:cs**********@trsvr.tr.unisys.com...
Thanks for the response.

Sp_depends seems to tell me what SPs reference a table or view.

I am trying to determine which SPs update a COLUMN in a table.

Any suggestions.


<snip>

You can execute sp_depends twice, first with the table name to find out
which procs update it, then with the proc names to find the updated columns:

create table dbo.t1 (col1 int, col2 int)
go
create proc dbo.p1
as update dbo.t1 set col1 = 2
go
create proc dbo.p2
as update dbo.t1 set col2 = 3
go

-- Get the proc names
exec sp_depends 't1'
go

-- Get the details of what each proc updates
exec sp_depends 'p1'
go
exec sp_depends 'p2'
go

drop table dbo.t1
drop proc dbo.p1
drop proc dbo.p2
go
Simon
Jul 23 '05 #5

P: n/a
Chad (ch**************@unisys.com) writes:
Thanks for the response.

Sp_depends seems to tell me what SPs reference a table or view.

I am trying to determine which SPs update a COLUMN in a table.


Have a closer look on sysdepends table. Here is a sample:

SELECT o.name, o2.name, c.name
FROM sysobejcts o
JOIN sysdepends d ON o.id = d.id
JOIN sysobjects o2 ON d.depid = o.id
JOIN syscolumns c ON d.depid = c.id
AND d.depnumber = c.colid
WHERE o2.name in ('this_table', 'that_table')
AND d.resultobj = 1
ORDER BY o.name, o2.name, c.name

Note that resultobj could also refer to an INSERT statement.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.