change column names

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)

This works.

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

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

if anyone can figure faster?

|||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

|||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.|||

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

|||

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

|||Yes you can, with syscolumns and sysobjects. See my post|||Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.