Mass rename

[Karin3]

We are in the process of migrating from sql 6.5 to 2000. We need to do a mass rename for user_id which exists in all tables and sp's in a very large database, and is now a reserved word in 2000. Any ideas on how we can manage this, or if it is possible to do a system wide rename or search and replace?
Thanx in advance
Karin

 

[fluteplr]

if this is a one time thing I would use the following technique.

You should be able to write a query using the information_schema.columns table like the following

set quoted_identifier off

select "sp_rename '"+rtrim(table_name)+".user_id','newfieldname','column'" from information_schema.columns where column_name = 'user_id'

run this in the query analyser have it return a text file. You can then use this output as the input of another query.

If you have views that include the user_id column you may need to join to the tables view so you can filter out the columns in the views since you can't tell that in the columns view.

As with any mass change like this.

MAKE A BACKUP FIRST!!!!!!

 

[tlbroadbent]

I refer below to SQL Server 7.0. I do not know if the problems noted still exist in SQL Server 2000. Perhaps a SQL 2000 expert can advise.

Using sp_rename in the manner suggested will likely result in a lot more problems than you'll want to deal with.
[ol][li]The method does not address stored procedures - only tables and views.
[li]Changes to Views are incomplete. The column in View output is changed by sp_rename but the column source is not changed.

Example:

Create View vMyData As

Select Col1, Col3, Col8, Col9
From MyTbl

Suppose I rename col3 as col22. If sp_rename is executed to change the column name in the table and the View, the View would cease to work because the source MyTbl.col3 would not exist. sp_rename does not change the select list in the view only the output column name! Bottom line is most Views will be broken after doing mass changes with sp_rename.[/ol]I hope this explanation is clear regarding the danger of using sp_rename.

I suggest an alternate way to address mass changes.
[ol][li]Create a SQL script for all views and stored procedures. Make sure you select to drop existing objects.
[li]Use sp_rename to rename the column in tables only.
[li]Use a text editor or Query Analyzer to change the name of the column in the script you created. You'll be able to find and change every occurrence of the column name fairly easily.
[li]Execute the script to drop and recreate each of the Views and Stored Procedures.[/ol]This method should be cleaner and provide the result you want.
Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker