change column names

change column names

• Dances With Data

  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.
  

• JavanehA

  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

  
  
  

• NorthSeattle

  Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.
  

• kitchen

  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

  
  

• Janga

  Yes you can, with syscolumns and sysobjects. See my post
  

• istanbul 05

  This works.

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

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

  if anyone can figure faster

  
  

• Daniel Upton

  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

  
  
• change column names