Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: Sp_rename or any

    Is there any other process to change the name of the column in a existing table for example table name is 'sample' and column name is 'code'
    i expected code by using alter or update commands
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ummm. Isn't the answer in your question header? dbo.sp_rename.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The other method is
    1) create temporary table for data
    2) copy data to temp table
    3) drop table
    4) re-create with new column definition
    5) copy data back in
    6) drop temp table

    Obviously we have to go further if we have constraints, etc.

    So just use sp_rename
    George
    Home | Blog

  4. #4
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by pootle flump View Post
    Ummm. Isn't the answer in your question header? dbo.sp_rename.
    i had already done that searching for a new code which uses alter or update
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by gvee View Post
    The other method is
    1) create temporary table for data
    2) copy data to temp table
    3) drop table
    4) re-create with new column definition
    5) copy data back in
    6) drop temp table

    Obviously we have to go further if we have constraints, etc.

    So just use sp_rename
    that's so funny ok i think we don't have such code for that right
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mathukumali View Post
    i had already done that searching for a new code which uses alter or update
    No - I mean look at the procedure called:
    dbo.sp_rename
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by pootle flump View Post
    No - I mean look at the procedure called:
    dbo.sp_rename
    you did not catch my question
    Dear pootle i had mentioned that i used exec sp_rename 'table'.'columnname', 'desired columnname'
    my question is that i want to use only alter or update commands to change the column name such as alter column_name or any thing else.
    just learning a little bit more on rename of column
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To be fair, you didn't mention that, but I think this is a case of a bit of confusion with the language.

    There is no ALTER syntax to change the name.

    George's method is the best. You could also:
    1) Create new column
    2) Update new column to value of old column
    3) Drop old column

    However, although it looks like less hassle there is a problem. I am stuck on something right now - I'll post again later.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    you are right i am not able to express my need in English which is difficult to me
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, there is a better method than George's:
    1) create new table for data with the new column name
    2) insert data into new table
    3) drop table
    4) rename new table to old table name

    Again you will need to account for all indexes and FKs
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    ok right now i got the point thanks pootle. Its really nice to see the other way thanks
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It should be pointed out that the other ways mentioned here would be exceedingly painful on a table that takes up several GB of space. For most things, you should aim to update the least amount of data possible. Since internally the columns are maintained by IDs, and relative position on each page, and the name of the column is just a label stored in a system table, just using the procedure to update the system table is the best way to do this. Updating system tables is not a good idea, and even the most seasoned DBAs will usually not do it, unless they have the backing of Microsoft Support. In fact, the more seasoned, the less likely they are to do it.

  13. #13
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by MCrowley View Post
    It should be pointed out that the other ways mentioned here would be exceedingly painful on a table that takes up several GB of space. For most things, you should aim to update the least amount of data possible. Since internally the columns are maintained by IDs, and relative position on each page, and the name of the column is just a label stored in a system table, just using the procedure to update the system table is the best way to do this. Updating system tables is not a good idea, and even the most seasoned DBAs will usually not do it, unless they have the backing of Microsoft Support. In fact, the more seasoned, the less likely they are to do it.
    just i am learning now. i will put your theme on my mind thanks
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ewwwwwwwwwwww.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Don't worry, Pootle. I am sure he did not mean it in a literal sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •