Post reply

How to Rename a Field in SQL Server 2008 by script ?

  • July 29, 2009 at 6:16 am

    #136826

    I have tried to rename a column name of a table through script (using alter, rename , modify) but could not achieve what i needed...

    Though this can be done easily using the "Design" but did not find how to do that with a script..

    Can anyone help me as to how to do that with script?

    Thanks,

    Amit

  • July 29, 2009 at 6:24 am

    #1031589

    what you want is the sp_rename function.

    with that, you can rename any object...table, constraint,column...doesn't matter.

    to rename a column, the syntax is like this:

    EXEC sp_rename 'SchemaName.TableName.ColumnName', 'NewColumnName, 'COLUMN';

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 29, 2009 at 6:29 am

    #1031594

    Thanx for your instant reply buddy,

    I dont have any problem with sp_rename syntax,

    bt i would like to know for the sake of my knowledge, if there is any other way to rename a column...?

    Thanks,

    Amit

  • July 29, 2009 at 6:37 am

    #1031605

    nope, no other way. even the GUI does that behind the scenes...the ALTER TABLE clause only lets you changing some of the other attrivutes for column, like ISNULL, datatype...but not the name.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 29, 2009 at 7:34 am

    #1031672

    Except of course for the hard way, create a new table with the new structure, drop all the constraints on the old table, copy the data over, put the constraints on the new table, drop the old table, rename the new table. Now the new column names are in place...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 29, 2009 at 9:17 pm

    #1032125

    Hi Grant,

    As admitted by you its the hard way, so this is not preferrable and also it can result in loss of data....

    Thanks,

    Amit

  • July 29, 2009 at 9:17 pm

    #1032126

    Your suggestions are always welcomed

  • July 30, 2009 at 5:30 am

    #1032303

    Amit (7/29/2009)

    Hi Grant,

    As admitted by you its the hard way, so this is not preferrable and also it can result in loss of data....

    Thanks,

    Amit

    Too true, but I always like to have options.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 30, 2009 at 5:41 am

    #1032312

    Hi Grant..

    Thanks a lot buddy and thanx to those who got involved in this discussion...

    Thanks,

    Amit

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply