Change Field Name via SQL - SQL Server Forums

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)

Change Field Name via SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-22 : 10:23:22

Fernando writes "Hello guys!!

I'm having big problems here!!

I would like to know How to change a field Name in a SQL SERVER Table

Example

I have table Product and a field Name

I would like to change the field Name to NameOfProduct via a SQL Query

I guess it would be using ALTER TABLE.. however.. I don't know..

Thanks"

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-22 : 10:44:12

One method...
1. Add a column called NameofProduct using Alter Table
2. Set the new column = to the name column using an update statement
3. Drop the Name column using Alter Table

Examples of Alter Table...
"Alter Table foo Add NameofProduct varchar(50)"
"Alter Table foo Drop Column Name"
Check BOL for more info on Alter Table

hth,
Justin

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-22 : 10:50:22

Another method is to use sp_rename.
Here's the example from BOL on how to rename a column:

B. Rename a column
This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-22 : 10:52:06

Little warning with sp_rename, that sucker will take a while to run if the columns big, but it is probably the best method

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-22 : 11:23:19

M.E. what do you mean "if the columns big"?

Does it take longer for certain data types?
Where can I find out more about this?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-22 : 12:01:06

I've ran it on a column that had alot of entries and was varchar(8000) and it was exactly instant. Thing took over 10 mins... although this may have been a locking issue and sql couldn't process it until another user released the table to my query... But I think it was becuase of the size

Lol, trust me.. I've been known to be wrong... So it mighta just been me

Edited by - M.E. on 05/22/2002 12:02:01

Fernando
Starting Member

1 Post

Posted - 2002-05-22 : 18:33:55

Hi guys..
I'm Fernando.. the guy that asked this question..

Ok..
I saw the SQL query using alter table..

However, using this way I'm gonna loose ALL information that is in the table Name...

Please... It must be a SQL query because I'm going to use this in my asp page...

So

"Alter Table foo Add NameofProduct varchar(50)"
"Alter Table foo Drop Column Name"

Doing that is ok.. But I will lose ALL information...

How can I copy the information that contains in the field Name to the new column without losing the INTEGRITY of my table?

Thanks!!!

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-23 : 11:07:22

quote:
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

That is a sql query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources