T-SQL script to rename tables

I need some script that could rename all the tables in my db.  I want to replace the spaces with underscores as follows:
From:
  Generic Traveler Steps
To:
  Generic_Traveler_Steps

Also, if possible, replace " - " with _ as follows:
From:
  Test - Pull Subtable
To:
  Test_Pull_Subtable

Is it possible to write T-SQL script to go through all of my 100 tables and do the above replacements?  If so, can you give me an example.  My DB is on SQL Server 2005.

Note: For testing and release purposes, I may need to do this renaming several time.  Otherwise, I would have just manually changed the name.
schmir1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Well the general T-SQL syntax for renaming a table is:

ALTER TABLE table_name RENAME TO new_table_name;

Therefore, you could write a dynamic SQL query that uses the INFORMATION SCHEMA views to construct and execute the above alter statement on each table that meet a certain criteria.
0
chapmandewCommented:
exec sp_msforeachtable 'declare @x sysname if charindex(?,'' '') > 0 begin set @x = replace(?, '' '', ''_'') exec sp_rename ''?'', @x end'
0
Aneesh RetnakaranDatabase AdministratorCommented:
run the results of this query

SELECT 'EXEC sp_Rename '''+TABLE_NAME+''','''+REPLACE (Table_Name,' ', '_') +''''
FROM INFORMATION_SCHEMA.TABLES
WHERE CHARINDEX(' ', Table_Name) > 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

chapmandewCommented:
whoops:

exec sp_msforeachtable 'declare @x sysname if charindex('' '', ?) > 0 begin set @x = replace(?, '' '', ''_'') exec sp_rename ''?'', @x end'
0
Kevin CrossChief Technology OfficerCommented:
LOL.  I was going to say sp_rename is probably better bet, but guess I am too slow.  Aneesh's example is what I meant by the dynamic SQL approach.

Tim's shows nice use of the for each table stored procedure, which I just have to get use to using.  Keep doing it the hard way. ;)
0
schmir1Author Commented:
aneeshattingal:
I tried your query first because it looked the easiest for me to understand.  I changed it to just do one table so I could see how well it worked.  Below is what I ran but it didn't change the table?
SELECT 'EXEC sp_Rename '''+TABLE_NAME+''','''+REPLACE (Table_Name,'Generic Traveler Steps', 'Generic_Traveler_Steps') +''''
FROM INFORMATION_SCHEMA.TABLES
WHERE CHARINDEX(' ', Table_Name) > 0 

Open in new window

0
chapmandewCommented:
You have to take the results from the query, paste them, and then run it.
0
schmir1Author Commented:
OK.  As you can see I'm a T-SQL novice.  Looks like it works now.  Will finish testing.
0
SharathData EngineerCommented:
instead of copy-pasting the result of the query suggested by aneeshattingal, you can create a loop and execute those statements.
declare @table as table(col nvarchar(2000))
declare @col nvarchar(2000) = ''
insert @table 
SELECT 'EXEC sp_Rename '''+TABLE_NAME+''','''+REPLACE (Table_Name,'Generic Traveler Steps', 'Generic_Traveler_Steps') +''''
FROM INFORMATION_SCHEMA.TABLES
WHERE CHARINDEX(' ', Table_Name) > 0
while @col is not null
begin
set @col = (select min(col) from @table where col > @col)
exec(@col)
end

Open in new window

0
schmir1Author Commented:
Sharath_123:
Good to know although now that I know how to use it, I like to see what changes are going to be made before they are made.

Some of my tables are blanks so I guess i don't need the where clause.

FYI: I would also like to modify the query to only list those tables that match the replace creteria.  I'll open up another questions for that one.
0
SharathData EngineerCommented:
>> Some of my tables are blanks so I guess i don't need the where clause.
you mean the data in the table or table name? what is blank here?
anyhow as you got solution for this question, you can close this and open another.
0
schmir1Author Commented:
Didn't know what I was doing.  The where is Ok and fixes the problem that I thought I had.  Thanks for all your help.
0
schmir1Author Commented:
Excellent Solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.