SQL script to rename all the table names with new name if it containes required string in mySQL and MsSQL

Question

I have one database with 100 tables in it. out of 100 some tables have name like House, House1, House2 , HouseXYZ and so on.

Now I want to write a script in MySQL and MsSQL to replace the House with Home. So my database should be having the table name Home, Home1, Home2, HomeXYZ and so on.

You sure you want to do that? Any existing objects which reference any tables will lose the reference. i.e you'd have to edit every function, procedure, trigger... — S3S, Jul 25 '16 at 15:38
Possible duplicate of [Rename all tables in database](http://stackoverflow.com/questions/2008412/rename-all-tables-in-database) — S3S, Jul 25 '16 at 15:49
Actually I am not sure how to handle this case. I mean how to do pattern matching and replace House with Home and rest of the string in table name should remain same. — user2425779, Jul 25 '16 at 16:46

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

As mentioned here you would you would do this for your table names:

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

edited May 23 '17 at 12:00

Community

1
1

answered Jul 25 '16 at 16:50

S3S

24,809
5
26
45

score 1 · Answer 2 · answered Jul 27 '16 at 06:23

--MSSQL

SELECT 'exec sp_rename @objname=' + NAME + ', @newname=' + replace(NAME, 'House', 'Home')
FROM sysObjects
WHERE type = 'U'
    AND NAME LIKE 'House%'

--MYSQL

SELECT CONCAT (
        'ALTER TABLE '
        ,table_name
        ,' RENAME '
        ,replace(table_name, 'House', 'Home')
        )
FROM information_schema.tables
WHERE table_name LIKE 'House%'

SQL script to rename all the table names with new name if it containes required string in mySQL and MsSQL

2 Answers2