SQL Table

• HBK_4700

  SSCrazy Eights

  Points: 8932

  More actions

  October 23, 2013 at 2:47 pm

  #278362

  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;

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  October 23, 2013 at 5:34 pm

  #1661122

  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);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001