HELP: Dropping a column damages a stored procedure

Question

Post reply

HELP: Dropping a column damages a stored procedure

gserdijn

Hall of Fame

Points: 3280
More actions
April 3, 2003 at 1:53 am

#79898

Using SQL Server 2000

See below for a oversimplified example. I am asked to drop a column, which might be used in one or more stored procedures.

Question: is there a way to find out the dependency between columns and stored procerdures?

I would appreciate any input.

Gerry

PS: I've found this problem also in the newsgroups, but never with a decent solution. But maybe I missed something...

-------------------------------------

use pubs

create table TEST (test_id numeric(12), code varchar(6))

go

insert into TEST values (1,'TEST1')

insert into TEST values (2,'TEST2')

go

create procedure up_showdata as

begin

select code from TEST

end

go

/*

exec up_showdata

alter table TEST drop column code

exec up_showdata

-- results in: Invalid column name 'code'.

*/

Dutch Anti-RBAR League

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

One way is to search through SYSCOMMENTS lookging for the table name and the column nae:

declare @table_name varchar(32), @column_name varchar(32)

select a.name

from sysobjects a, syscomments b

where charindex(@table_name,b.text) > 0

and charindex(@column_name,b.text) > 0

and a.is = b.id

This will return a list of procedures containing both the table name and column name. I guess you then have to go through them so find the actual usage

Jeremy