Rename all tables in database

Question

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

Answer 1

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.