Any way to gradually change a column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Any way to gradually change a column name

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-22 : 20:13:44

I've got a 'ton-o-procedures' that reference a column with a misleading name.

Is there a way to declare an alias name to that column so the procedures can be revised one at a time to use the new column name?

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-22 : 20:21:28

quote:

How to Gradually a Column Name

I'd say "English Sam, do you speak it" but people take it the wrong way.
You could maybe use a computed column for selects, but it won't help for updates and inserts.
Why don't you just run a search and replace through all your stored procedure scripts and rerun them ?

You DO have all your procs in script form don't you ?

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-22 : 20:41:39

This is one of those problems that's not as easy as it might seem. The misleading column name is also used in other tables, but not for a misleading purpose. I don't want to change those column names, or alter the procs that reference them.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-22 : 20:43:38

You could rename the table and use a view. The view would reference either column name.
e.g.

create table test (i int)

create view a
as
select i,j = i from test

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-22 : 21:20:42

Thanks Nigel. Looks like it ought to work fine.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-17 : 12:42:39

It's just amazing how many times I just go...like, WOW

I have NEVER seen that before....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-17 : 12:46:58

Doesn't Nigel's solution have a problem of losing access to indexes since it's a VIEW?

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-17 : 14:25:03

SHOW PLAN gives me an index seek


USE Northwind
GO

create table test (i int)
GO

CREATE INDEX text_ix1 ON Test(i)
GO

create view a 
as
select i,j = i from test
GO

insert into test select 1
GO

select * from a WHERE j = 1
GO

DROP VIEW a
DROP TABLE test
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources