Renaming all tables in a Database

Question

Post reply

Renaming all tables in a Database

brekher

SSCommitted

Points: 1514
More actions
March 13, 2008 at 6:36 am

#71281

I have a database with almost 1000 tables that are prefixed with tbl_. I need to go through all of them an rename them to exclude that prefix. Any help would be appreciated. Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 1

Do a Google search for sp_MSForEachTable...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bloody Benny SSC Rookie Points: 43 More actions · Answer 2

What is with the tbl_ prefix... they do that at my company too. What a waste. Looks ugly and I don't see any point - we KNOW they're tables!

Anyway: here's my method:

---

DECLARE @tname varchar(300)

DECLARE @newname varchar(300)

DECLARE tables CURSOR FAST_FORWARD

FOR

SELECT name FROM sysobjects WHERE xtype='U' AND name LIKE 'tbl_%'

OPEN tables

FETCH NEXT FROM tables INTO @tname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @newname = SUBSTRING(@tname, 5, 300)

PRINT 'Renaming ' + @tname + ' to ' + @newname

EXEC('sp_rename '''+@tname+''', '''+@newname+'''')

FETCH NEXT FROM tables INTO @tname

END

CLOSE tables

GO

---

Each execution of sp_rename will give a little error, but nothing to worry about.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Use the ms_foreachtable or just write a cursor that loops through the output from

select table_name

from information_schema.tables

where table_name like 'tbl_%'

brekher SSCommitted Points: 1514 More actions · Answer 4

brekher

SSCommitted

Points: 1514

March 13, 2008 at 9:26 am

#789145

Thanks everyone! All tables are now renamed 🙂

TraderSam SSCrazy Points: 2931 More actions · Answer 5

Just curious, do any of these processes also 'fix' all the stored procedures that query these tables, or do you have to do that some other way?

If it was easy, everybody would be doing it!;)

brekher SSCommitted Points: 1514 More actions · Answer 6

Hi,

For that I went in and replaced the names being referenced using a script I downloaded from the sql scripts on this site.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

brekher (3/13/2008)
I have a database with almost 1000 tables that are prefixed with tbl_. I need to go through all of them an rename them to exclude that prefix. Any help would be appreciated. Thanks!

Here's how to do it without any Cursors:

Declare @sql varchar(8000) --change to Varchar(MAX) on Sql2005!

Select TOP 100 @sql = @sql+'sp_rename ''' + table_name + ''', '''

+ Substring(table_name, 2, 255)+ '''

'

From INFORMATION_SCHEMA.Tables

Where table_name Like 'tbl_%'

Print 'Executing:'

Print @sql

Exec (@sql)

Of course you are limited to 8000 characters on SQL 2000, so I have limited it to change only 100 tables at a time. Just rerun it until it says that there are no records found.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]