Renaming multiple tables

sql-server, sql-server-2008

Solution

You can use Alter Schema with an undocumented Stored Procedure exec sp_MSforeachtable which basically iterates through all the tables .

  exec sp_MSforeachtable "ALTER SCHEMA new_schema TRANSFER ? PRINT '? modified' "

change the new_schema keyword with your new Schema .

For details please go through the link

sp_MSforeachtable

Alter Schema for all the tables

As others have pointed out that the SP is deprecated so There is another way to do this by getting the names of the table from sys.tables

Declare @value int
Set @value=1
declare @sql varchar(max), @table varchar(50), @old varchar(50), @new varchar(50)

set @old = 'dbo'
set @new = 'abc'

while exists(select * from sys.tables where schema_name(schema_id) = @old)

begin
;With CTE as
 (
  Select *,row_number() over(order by object_id) rowNumber from sys.tables 
  where schema_name(schema_id) = @old
 )
 select @table= name from CTE where @value=rowNumber
 Set @value=@value+1

 set @sql = 'alter schema ' + @new + ' transfer ' + @old + '.' + @table

 exec(@sql)
 end

Problem

In SQL Server, I have a database `abc`. In this database I have hundreds of tables. Each of these tables is called `xyz.table` I want to change all the tables to be called `abc.table`. Do we have a way by which I can change all the names from `xyz.table` to `abc.table` in database `abc`? I am able to manually change the name by changing the schema for each table to abc

Original source