SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Rename tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
332 Posts

Posted - 10/23/2013 :  17:06:17  Show Profile  Reply with Quote
Hi, I would like to rename more than one table (1000+) which starts with WD_TABLENAME. I have the below script but is there any better one to do this?

select
'exec sp_rename ''' + [name] + '''WD_'',' + [name] + ''';' as SSQLCmd
into #temp1
from sys.tables

declare @sSQL varchar(8000)

declare cur1 cursor for
select SSQLCmd from #temp1

open cur1
fetch next from cur1 into @sSQL

while @@FETCH_STATUS = 0
begin
print convert(varchar(25),getdate()) + ' - Executing: ' + @sSQL
print 'Exec (@sSQL);'
fetch next from cur1 into @sSQL
end

close cur1;
deallocate cur1;

drop table #temp1;

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/23/2013 :  17:51:54  Show Profile  Reply with Quote
Are you only printing the execute statements, or are you also executing them in the cursor?

Rather than use a cursor to run execute statements, I would runthe first query that generates the rename statements and instead of inserting it into a temp table, copy it from the SSMS results window to a query window and execute that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/24/2013 :  01:30:24  Show Profile  Reply with Quote
Use query like

SELECT 'Exec sp_rename ''' + TABLE_NAME + ''',''WD_' + TABLE_NAME + ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE ....your condition


then copy result, paste to new SSMS window and execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/24/2013 :  03:24:09  Show Profile  Reply with Quote
Refer
http://www.sqlservercentral.com/Forums/Topic1445241-392-1.aspx

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000