Post reply

How do you rename a table using t-sql

  • January 4, 2008 at 8:45 am

    #181908

    i have an archive server to hold old data that we want to keep but will be accessed maybe once a year or so. i want to start doing monthly dumps of some small databases there that hold business rules. we have never needed it before, but recently we needed the old data for testing something and we didn't have it. no biggie, but since the server will have around 5TB or more there is storage to burn.

    i want to create an SSIS package using transfer sql server objects and then run a sql task to rename the table at the destination to dbname_tblname_date. reason for this is we have close to 10 databases for different business rules and all the tables have the same name and i want to dump it all into one archive database.

    the first SSIS task is easy, but i can't seem to find the syntax to change the table name once the data is there

  • January 4, 2008 at 8:52 am

    #764877

    there's a system stored procedure that does that, call sp_rename

    As I recall - syntax is

    sp_rename @objname='oldname' @newname='newname'

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 4, 2008 at 10:00 am

    #764901

    As Matt said check sp_rename in BOl..

    sp_rename [ @objname = ] 'object_name' ,

    [ @newname = ] 'new_name'

    [ , [ @objtype = ] 'object_type' ]

    SQL DBA.

  • January 4, 2008 at 10:01 am

    #764902

    thank you all

    working on the code now to rename it with a dynamic name

Viewing 4 posts - 1 through 3 (of 3 total)

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