locked
change column names RRS feed

  • Question

  • i am trying to change column names in a bunch of tables.

    why is this not right?

     is there any sp that i can use as i have to change this in  a lot of tables across two databases?

    Alter Table Answers

    Change Product NewProduct varchar(35)

    Tuesday, January 31, 2006 6:55 PM

Answers

  • Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.
    Tuesday, February 7, 2006 1:59 PM

All replies

  • This works.

    EXEC sp_rename 'Answers2.[Product]', 'NewProduct', 'COLUMN'

    now how do i do multiple columns.......

    if anyone can figure faster????

    Tuesday, January 31, 2006 7:10 PM
  • You can't, just execute a batch query (a normal query with more queries in it) like this:


    EXEC sp_rename 'Answers2.[Product]', 'NewProduct1', 'COLUMNA' GO

    EXEC sp_rename 'Answers3.[Product]', 'NewProduct2', 'COLUMNB' GO

    EXEC sp_rename 'Answers4.[Product]', 'NewProduct3', 'COLUMNC' GO
     

    Tuesday, January 31, 2006 9:47 PM
  • You can do some very clever things with sysobjects and syscolumns, provided all the column names are the same that you want to rename. Write a stored procedure that gets a list of the tables that contain that column. Then write a dynamic sql statement or just a standard sql statement that ouputs an alter command for each row returned. Check the syntax of the coded created and execute if it is OK.
    Monday, February 6, 2006 11:17 PM
  • select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

    from syscolumns col

    join sysobjects obj

    on col.id = obj.id

    where col.name = 'Product'

    -------------------------------------------------------------------------

    That will output a load of dynamic SQL. Now copy and paste into Query Analyser or the query execution window, test and execute.

    For more SQL Server tips, check out my blog:

    http://blogs.claritycon.com/blogs/the_englishman/default.aspx

    HTH

    Tuesday, February 7, 2006 3:37 AM
  • You may need to alter the query to:

    :

     

     

    select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

    from syscolumns col

    join sysobjects obj

    on col.id = obj.id

    where col.name = 'Product'

    and obj.[type] = 'U'

    -------------------------------------------------------------------------

    To ensure only tables are returned from sysobjects

    Tuesday, February 7, 2006 3:48 AM
  • Yes you can, with syscolumns and sysobjects. See my post
    Tuesday, February 7, 2006 3:48 AM
  • Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.
    Tuesday, February 7, 2006 1:59 PM