Rename tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Rename tables

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2013-10-23 : 17:06:17

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 17:51:54

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 01:30:24

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-24 : 03:24:09

Refer
http://www.sqlservercentral.com/Forums/Topic1445241-392-1.aspx

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources