Menu
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?
605
Replace database_name and table_name below with database and field names respectively. alter table database_name. table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; If you want to change collation of all tables in your database, you need to run the above query for each table separately.
To get table names using SELECT statement, use “information_schema. tables”. Let us see an example, wherein we have a database that contains 3 tables. The syntax to get all table names with the help of SELECT statement.
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:
Caution: Changing any part of an object name could break scripts and stored procedures.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With