CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlddl
VilemRousi
VilemRousi

Reputation: 2142

SQL Server 2008 R2 - How to rename multiple columns name?

I haven´t much experiences with SQL Server 2008, I´d like to rename all columns in my database (add to existing name some prefix).

I'm thinking something like:

Foreach( column in table)
(
    column_name = prefix+column_name
)

Is there any way to do it??

Thanks so much.

Upvotes: 1

Views: 6049

Answers (2)

NET Experts
NET Experts

Reputation: 1536

you may try using sp_RENAME. it does allow only single column at a time. but you can do it on your code to loop on each of the columns. hope it helps. thanks!

Upvotes: 1

Alex_L
Alex_L

Reputation: 2654

You can try the following solution: iterate through records from sys.columns table using cursor and for each record excute the sp_rename system stored procedure. I think there can be problems with integrity while renaming, so you need to be careful with other objects (for example stored procedures) that refers for these columns.

UPDATE: MVP says:

if you have any indexes, primary keys, unique keys, or foreign keys, they need to be dropped, and then readded, after the columns have been renamed. If you don't do this in the correct order, you will get lots of nasty error message.

Upvotes: 4

Related Questions