Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft SQL Server
Reload this Page

ALTER TABLE problem!!

Discuss ALTER TABLE problem!! in the Microsoft SQL Server forum on ASP Free.
ALTER TABLE problem!! Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old July 15th, 2004, 10:07 AM
stevemw stevemw is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Location: uk
Posts: 70 stevemw User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
ALTER TABLE problem!!
The below doesn't seem to be supported on SQL 2000 or Access.

ALTER TABLE TableName RENAME OldField TO Newfield

Is there a way I can alter a column name without going into the design of the table? I want to supply a client of my company with a SQL statement to alter a table name, rather than allowing them to see the table design in Enterprise Manager. I can get round it by trying the below, but it's a bit long winded.

ALTER TABLE TableName ADD NewField Char(20)

UPDATE Tablename SET NewField=OldField

ALTER TABLE Tablename DROP COLUMN OldField


Cheers

Steve
Reply With Quote
  #2  
Old July 15th, 2004, 10:37 AM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
Click here for more information.
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,781 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470
Alter a table name or alter a column name?
Reply With Quote
  #3  
Old July 15th, 2004, 10:56 AM
stevemw stevemw is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Location: uk
Posts: 70 stevemw User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
I'm lookin to alter a column name.
Reply With Quote
  #4  
Old July 15th, 2004, 12:55 PM
Memnoch's Avatar
Memnoch Memnoch is offline
Unholy Moderator
Click here for more information.
  
Join Date: Oct 2003
Location: In hell, where did you think?
Posts: 11,781 Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Memnoch User rank is Lieutenant Colonel (40000 - 50000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470
You can do it like this
Code:
sp_rename 'table.old_fieldname', 'new_fieldname', 'COLUMN'

Example:
This will rename the JobID column in an Employers table, to JobNum.
Code:
sp_rename 'Employers.JobID', 'JobNum', 'COLUMN'
Reply With Quote
  #5  
Old July 16th, 2004, 03:54 AM
stevemw stevemw is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Location: uk
Posts: 70 stevemw User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 5
Excellent. Thanks for that
Reply With Quote
  #6  
Old July 30th, 2004, 01:18 PM
kamsuresh kamsuresh is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Posts: 1 kamsuresh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
ALTER TABLE TableName RENAME OldField TO Newfield
You can change it through Enterprise manage can't you ?


Quote:
Originally Posted by stevemw
The below doesn't seem to be supported on SQL 2000 or Access.

ALTER TABLE TableName RENAME OldField TO Newfield

Is there a way I can alter a column name without going into the design of the table? I want to supply a client of my company with a SQL statement to alter a table name, rather than allowing them to see the table design in Enterprise Manager. I can get round it by trying the below, but it's a bit long winded.

ALTER TABLE TableName ADD NewField Char(20)

UPDATE Tablename SET NewField=OldField

ALTER TABLE Tablename DROP COLUMN OldField


Cheers

Steve
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft SQL Server > ALTER TABLE problem!!

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway. 
Stay green...Green IT