rename TSQL

Posted on 2014-08-14
Last Modified: 2014-08-15

Hello All,
Hello All,

TSQL – renaming tables logic – I am looking for a renaming table logic

if you find 2013 as part of any columns names or one column within a table “rtyu_25”–
then if that table name doesn’t have  a “_13” at the end of table name>>
 then rename as table name + “_15”
 do nothing
Question by:Rayne
    LVL 23

    Assisted Solution

    Not sure I understand the logic you are describing.

    1) Any column name has "2013" in it *OR* any column starts with "rtyu_25"
    2) Table name must *not* end in "_13"

    If so, execute this in SSMS text mode and then cut/paste the output in a new query and run it.

    	'EXEC sp_rename '''
    	+ t.NAME
    	+ ''', '''
    	+ t.NAME
    	+ '_15'';'
    FROM sys.columns AS c
    	INNER JOIN sys.tables AS t
    		ON c.object_id = t.object_id
    WHERE (
    		c.NAME LIKE '%2013'
    		OR c.NAME LIKE 'rtyu_25'
    	AND t.NAME NOT LIKE '%_13';

    Open in new window

    LVL 47

    Assisted Solution

    AFTER making a backup, yikes
    LVL 23

    Expert Comment

    For sure, PP!
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    Why do you want to rename a table? Worst, not one but many!

    Do you know the implications of this operation? If the table have dependencies they won't work anymore until you rename them also.

    If you could tell us what do you want to achieve maybe we can point you to a better solution.

    Author Comment

    Hello all,

    Thanks to everyone for your very wise suggestions. The issue is I have two ssis jobs that are year specific. So one job refers to files that RE OF 2013 and the other (identical ) job refers to files of 2014. Now after the data is imported into the DB, it undergoes several transformation. So for example staging table for dataB is named stage1. Note: all the 12 months are across the first row as column headers for a specific metric.
    Table 1 (specific to 2013) >>
    Account | product |2013P1 |2013P2|……….2013P12

    Table 1 (specific to 2014) >>
    Account | product |2014P1 |2014P2|……….2014P12

    Now the way I was thinking is keep two tables of identical structure but different column heade4rs and then have two tables – stage1_14
    Stage1 is the main staging table that imports in the data for a specific metric
    When I am running the ssis job specific to 2013, I will rename stage1_13 to stage1
    When I am running the ssis job specific to 2014, I will rename stage1_14 to stage1

    Let me know if I am thinking too complex or making my life difficult….
    Also what is some good practice when I have work with different years of same data metrics at the same time? Please advise.
    LVL 23

    Accepted Solution

    If it were me, I would use "Tasks -> Generate Scripts" to generate the table structures you have now and then just use Search/Replace in a text editor to get the SQL to create new, empty, tables with the names you need.  Keep that script around and just use it year after year (updating for any schema changes you make, of course).

    Author Closing Comment

    thank you , thank you
    this is a life saver :)
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    You can use the script and modify it to create the tables with the current year on the name:
    - Just concatenate the table name with YEAR(GETDATE())

    Then you won't need to change it every year.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now