October 23, 2013 at 2:47 pm
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;
October 23, 2013 at 5:34 pm
From your example code it's hard to determine what you are changing the table names to. Below is a script that works without any cursors or loops to rename tables that begin with "WD_" to the same name minus the "WD_". E.g. "wd_table_01" becomes "table_01. "
-- using my tempdb as a sandbox...
USE tempdb
GO
CREATE TABLE wd_table01(x int)
CREATE TABLE wd_table02(x int)
CREATE TABLE wd_table03(x int)
GO
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
SELECT TABLE_NAME AS tbl
INTO #tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'wd_%'
DECLARE @sql varchar(8000);
SELECT @sql=
(SELECT 'exec sp_rename '''+tbl+''', '''+REPLACE(tbl,'wd_','')+''';'+CHAR(10)
FROM #tables
FOR XML PATH(''));
EXEC(@sql);
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply