HELP: Dropping a column damages a stored procedure
-
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 -
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
-
quote:
One way is to search through SYSCOMMENTS ...Jeremy,
Thanks for your quick solution. It was exactly what I needed.
Gerry.
Dutch Anti-RBAR League -
Nice to know this way Jeremy.
I has similar problem, and been using DBArtisan table dependencies page for this.
.
-
Excellent Jeremy
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply