Finding Stored Procs that Ref/Update a column

[Chad]

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.

[Simon Hayes]

"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

[Chad]

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

[QueryBuilder]

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"

[Simon Hayes]

"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

[Erland Sommarskog]

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