rename TSQL


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
Who is Participating?
nemws1Database AdministratorCommented:
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).
nemws1Database AdministratorCommented:
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
		c.NAME LIKE '%2013'
		OR c.NAME LIKE 'rtyu_25'
	AND t.NAME NOT LIKE '%_13';

Open in new window

AFTER making a backup, yikes
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

nemws1Database AdministratorCommented:
For sure, PP!
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
RayneAuthor Commented:
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.
RayneAuthor Commented:
thank you , thank you
this is a life saver :)
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.