Rename multiple tables based on create_date

Posted on 2013-01-09
Last Modified: 2013-01-09
I have multiple tables that were created on a specific date. They are currently dbo.whatevername. I want them to be xxxlib.whatevername. How do I write sql code to accomplish this?
Question by:qbjgqbjg
LVL 22

Accepted Solution

Steve Wales earned 250 total points
ID: 38759310
First thing to do would be to create the schema xxxlib (if it doesn't already exist).

I'm sure you could write code to copy the tables all automatically.   If I had to perform a copy like this, I'd want to generate the code to do it and carefully review it before executing it.

So, something like this (probably not the prettiest way to do it, but I personally prefer to carefully review such things before executing):

select 'select * into xxxlib.'+name+' from dbo.'+name+';'
from sys.tables
where schema_id = 1
and convert(char(8), create_date, 112) = '20130103'

Open in new window

Hit Control-T in SSMS to sent output to text, then execute.

In the output window you have a script you can verify for correctness before running.

Make sure your users are locked out and what not before executing such a thing and of course you would need to make sure proper permissions were set on the new tables.
LVL 41

Assisted Solution

ralmada earned 250 total points
ID: 38759375
It seems to me that you want to rename the schema, not create copies of the tables from one schema to another. I would use the ALTER SCHEMA then. Run:

declare @strSQL varchar(max)
set @strSQL = ' '
SELECT @strSQL = @strSQL + 'ALTER SCHEMA newschema TRANSFER dbo.' + quotename(name) + ';'
FROM sys.objects
WHERE schema_id = 1

print @strSQL -- to check the intended output is correct

-- execute(@strSQL) --uncomment to make the changes once you're sure.

Author Closing Comment

ID: 38759524

Expert Comment

ID: 38759547
You can try the following script

--USE Test01


set @Change = 'ALTER SCHEMA TestSchema TRANSFER dbo.['

	 @SQL = COALESCE(@SQL + @Change + [name] + '] ', @Change + [name] + '] ')
	--create_date = Your condition



Open in new window


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

13 Experts available now in Live!

Get 1:1 Help Now