Finding all references to a column

[rnewman]

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?

[Erland Sommarskog]

Re: Finding all references to a column

[posted and mailed, please reply in news]

rnewman (newmanr19@yaho o.com) writes:[color=blue]
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?[/color]

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl' )
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp