Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: Changing a column name

    How can you change a Column name for MS SQL 2000 ?

    something like :

    ALTER TABLE [dbo].[MyTable]
    OldName RENAME NewName
    GO

    thank you

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    From BOL,
    Code:
    --This example renames the contact title column in the customers table to title.
    
    EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    thanks a lot

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So why does EM do this?

    Code:
    EXECUTE sp_rename N'dbo.myTable00.AccID', N'Tmp_AccID_new', 'COLUMN'
    GO
    EXECUTE sp_rename N'dbo.myTable00.Tmp_AccID_new', N'AccID_new', 'COLUMN'
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Mainly for backward compatibility, I would guess. SQL 7.0 might not have supported renaming columns.

  6. #6
    Join Date
    Jun 2005
    Posts
    115
    EM is really a strange application
    SELECT CASE doesn't work for example

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by quentin
    EM is really a strange application
    SELECT CASE doesn't work for example
    You mean in the View window....

    ALL of that stuff should be done in Query Analyzer.

    I like to see what EM does when you change a table..they usually try to provide the best practices approach.

    For example, go change the order of a column, or drop a column in EM, and then save the script....

    Know what it does? It doesn't use ALTER, and there's a very good reasonm for that.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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