Rename all tables in database

I have a database where all of the tables are prefixed with a set of the same characters. This was done because at one time they were in a shared database set up for pet projects with hundre开发者_StackOverflowds of other tables. The application, and thus the database, is now ready to be moved out of that phase and ready to be out on it's own. I would like to remove the prefix for each of the tables. Is there an easier way to do this rather than right-clicking and renaming each table individually?

select 'exec sp_rename @objname=' + name + ', @newname=' + replace(name ,'prefixedchars', '')
from sysObjects
where type = 'U'

The results from this will be something like:

exec sp_rename @objname=prefixedcharsTable1, @newname=Table1
exec sp_rename @objname=prefixedcharsTable2, @newname=Table2
exec sp_rename @objname=prefixedcharsTable3, @newname=Table3
etc... for each table in your db

All you have to do is copy those statements into a new query window and run.

Caveats:

• You will get an cautionary message as follows: Caution: Changing any part of an object name could break scripts and stored procedures.
• You will have to rename the tables in any stored procedures, functions, views, and triggers.

You could write a script to look at the meta information and change it, but won't this ruin all your SPs and Parameterized Queries?

You could do something like script the whole database and do string replacement on the script, run it on a new database, and then import the data. This of course depends on how well you can match the string to be replaced. You might have to make corresponding changes in your application too. You could always try something like this on a test database to see if it would work.

Take a look at this too - Mass Renaming of Tables and Stored Procedures

One method has some cost and the other is similar to my suggestion.

First, write a stored procedure that has a table name parameter and uses string parsing and ALTER TABLE to remove the prefix from the specified table.

Then, use sp_MSforeachtable to call that procedure for each table.

Reference: http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

Purely from the "replacement for right-click, rename" perspective (not renaming references, procedures, views, etc) - mix it up a little. Get the list of tables:

select name from sys.Tables

copy and past it into emacs (or your favourite regex line editor) and run a regex line replacement: search\(xyz.*\) and replace with SP_RENAME 'xyz\1', '\1'; This repalces the table xyzFoo with Foo. Paste the lines back into SQL Server and run it.