p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/)
-   Classic ASP Databases (http://p2p.wrox.com/classic-asp-databases-62/)
-   -   Rename a Column using SQL (http://p2p.wrox.com/classic-asp-databases/33654-rename-column-using-sql.html)

pipelineconsulting September 23rd, 2005 12:56 PM

Rename a Column using SQL
 
I have an Access database and wish to change the name of a table column from within an ASP.NET application.

I would prefer to use an SQL statment and an OleDbCommand to do this and then ExecuteNonQuery() etc.

Two possible SQL statements are:

Code:

ALTER TABLE tablename RENAME COLUMN OldName TO NewName
or
Code:

ALTER TABLE tablename RENAME OldName TO NewName
Neither of these statements work and I get an SQL Syntax Error.

Does anyone know what the correct SQL statment is? I can add and remove columns OK using similar SQL statements, just can't rename a column/field!

Cheers

Graham Wilson


zawmn83 September 12th, 2007 04:34 AM

EXEC sp_rename
    @objname = 'table_name.old_column_name',
    @newname = 'new_column_name',
    @objtype = 'COLUMN'


dparsons September 12th, 2007 08:08 AM

1. The post is 2 Years old.
2. sp_rename will work in SQL Server, not in access.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

KansasFiddler September 17th, 2009 01:55 PM

YOUR post is 2 years old. nanny Nanny PooPoo!!

rfulani May 20th, 2011 07:17 AM

DO it
 
Quote:

Originally Posted by pipelineconsulting (Post 33654)
I have an Access database and wish to change the name of a table column from within an ASP.NET application.

I would prefer to use an SQL statment and an OleDbCommand to do this and then ExecuteNonQuery() etc.

Two possible SQL statements are:

Code:

ALTER TABLE tablename RENAME COLUMN OldName TO NewName
or
Code:

ALTER TABLE tablename RENAME OldName TO NewName
Neither of these statements work and I get an SQL Syntax Error.

Does anyone know what the correct SQL statment is? I can add and remove columns OK using similar SQL statements, just can't rename a column/field!

Cheers

Graham Wilson



to rename

for eg chenge column name for fnmae to fname in table employee

step 1 rename employee to employe
step 2 create table employee (fname,lname , and other columns of table) as select * from employe.

then drop employe
and add constraints againn to employee if any exist.

alter table do not change name of column , table and size of column if table data exist

jonarmani September 16th, 2013 11:11 AM

Tradition
 
I feel obligated to revive this thread, as part of a bi-annual tradition started in September of 2005. This thread was then bumped in September 2007, September 2009, and May 2011. From September 2013, I look forward to the response from a tradition-loving soul in September 2015.

Long live sp_rename!

jonarmani September 30th, 2015 12:05 PM

Tradition Continues
 
Happy 10 years to this thread! And once again, a 2 year gap between posts! And once again, long live sp_rename! [:D]

jonarmani September 21st, 2017 05:16 PM

The Tradition Never Ends
 
Happy 12th! Seeya in another two years!


All times are GMT -4. The time now is 11:45 PM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.