Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mistux
Yak Posting Veteran
90 Posts |
 Posted - 2005-08-26 : 12:05:38
|
| I have just "upsized" my old Access MDB data into MS SQL server, but I want to now take the opportunity to clean up the table names.Currently all the tables that were imported into MS SQL start with the prefice: "T-" and I want to change them all to "tbl_"I have two questions:1. Is there a way to write a stored procedure that will rename all the tables in the database from "T-"xxx to "tbl_"xxx ?2. Some of the tables just plan need to be renamed becuse of things like spelling mistakes etc. Is there a way for me to write a stored procedure so that I can enter all the tables that neeed to be renamed and then the new names for them, all into one sp?Thanks.MichaelFor all your DNC needs for your CNC machines. www.mis-group.com |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-26 : 14:04:37
|
There are no existing applications or processes that will break when you change all the table names, right? The code to change an object name is quite simple: sp_rename '<currentName>', '<newName>' (see Books Online)you could generate the code easily using information_schema. --rename all table that begin: "T-"select 'exec sp_rename ''' + table_name + ''', ''' + 'tbl_' + right(table_name, len(table_name)-2) + ''''from information_schema.tables where table_type = 'BASE TABLE'and table_name like 'T-%' alternatively you could also script out the database tables and use a text editor to globaly replace table names.I'm not sure why you want this as a stored procedure but it would be easy enough to do if you want.Be One with the OptimizerTG |
 |
|
|
mistux
Yak Posting Veteran
90 Posts |
Posted - 2005-08-27 : 17:30:00
|
| Thanks TG I ended up usingsp_rename '<currentName>', '<newName>' The reason I wanted it as a stored procedure was because that was the only way I new how to run 157 renames, by putting them all in one sp and running that.MichaelFor all your DNC needs for your CNC machines. www.mis-group.com |
|
|
|
|
|
|
How to Rename All Tables using a SP