Renaming Tables procedures and references in MS SQL Database

Hi All,

Today i will show you all an interesting trick which will rename all the Tables, Procedures and most of all the table names in procedures i.e. All references of the tables are updated at the SQL level.

This might help you out when a specific prefix is required with all tables as you might have forgot to add tbl_ before tables and SP_ before procedures but at some point of time you need to fix that.

So here we go:

The first thing would be using a Query that will rename all the tables:

select 'exec sp_rename ''' + table_schema + ','+ table_name + ''', ''' + 
  'tbl_' + table_name + ''''
from information_schema.tables 
where table_type = 'BASE TABLE'

When i applied that to master DB following is what i got.

exec sp_rename 'dbo,spt_fallback_db', 'tbl_spt_fallback_db'
exec sp_rename 'dbo,spt_fallback_dev', 'tbl_spt_fallback_dev'
exec sp_rename 'dbo,spt_fallback_usg', 'tbl_spt_fallback_usg'
exec sp_rename 'dbo,spt_monitor', 'tbl_spt_monitor'
exec sp_rename 'dbo,spt_values', 'tbl_spt_values'
exec sp_rename 'dbo,MSreplication_options', 'tbl_MSreplication_options'

Following is what you have to use for Renaming Procedures.

SELECT 'exec sp_rename ''' + SPEcific_Schema +'.'+ SPECIFIC_NAME + ''', ''' + 
  'SP_' + SPECIFIC_NAME + ''''
FROM information_schema.routines
WHERE ROUTINE_TYPE = 'PROCEDURE'

And now comes the tricky part, i like to use Excel concatenate function for this but you can use any way you like.

First get the text for all the procedures by using.

select * from syscomments
WHERE number = 1

once the text is with you copy all the names of Old and New tables in Excel sheet and write the formula,

Note that i have Written two formula one for the top row and one for all renaming rows as shown in screen shot below.







Now Paste the rows in SQL management studio and Add replace keywords equal to number of table names and you get the following result.

SELECT 
replace(
replace(
replace(
replace(replace(text,'dbo,spt_fallback_db','tbl_spt_fallback_db'),
'dbo,spt_fallback_usg','tbl_spt_fallback_usg'),
'dbo,spt_monitor','tbl_spt_monitor'),
'dbo,spt_values','tbl_spt_values'),
'dbo,MSreplication_options','tbl_MSreplication_options')
FROM syscomments
WHERE number = 1

Don't forget to replace the "Create Procedure" command with "Alter Procedure" when replacements of tables are done.

Execute all the queries in following order.

1. Execute Rename for tables
2. Execute Rename for Procedure.
3. Execute Query name from Excel.

and you are done :)

Happy coding.