Post reply

Renaming all tables in a Database

  • 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!

  • March 13, 2008 at 7:39 am

    #789032

    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)
    Intro to Tally Tables and Functions

  • March 13, 2008 at 7:41 am

    #789035

    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.

  • March 13, 2008 at 7:56 am

    #789049

    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_%'

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 13, 2008 at 9:26 am

    #789145

    Thanks everyone! All tables are now renamed 🙂

  • March 13, 2008 at 10:54 am

    #789208

    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!;)

  • March 13, 2008 at 11:12 am

    #789221

    Hi,

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

  • March 13, 2008 at 3:01 pm

    #789330

    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]

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

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