Renaming multiple tables

Question

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

Vinnievinnitsa

Hi. What version of SQL-server do you use? Am I right that you want to change schema of all tables in a database? – Julius

Thanks @Igor for looking into. I am using sql server 2008. You are right i want to change schema of all the tables – Vinnievinnitsa

Answer 1

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

Procedure

What if the database has tables in the xyz schema, but also in a def schema, and the OP wants to move only those tables from the xyz to abc ? – Ewold

marc_s : I have updated my reply .Without using cursor we can change the schema . – Procedure

That might work, too - in my testing, however, it ended up in an endless loop for the last table..... also: being really nitpicky: the input to your exec() function should be of type nvarchar(...) (but that's really really nitpicky, agreed!) – Ewold

Thanks Marc_s .Agreed i should be changing it to nvarchar – Procedure

Answer 2

You could have a cursor run over all your tables in the xyz schema and move all of those into the abc schema:

DECLARE TableCursor CURSOR FAST_FORWARD 
FOR
    -- get the table names for all tables in the 'xyz' schema
    SELECT t.Name
    FROM sys.tables t 
    WHERE schema_id = SCHEMA_ID('xyz')

DECLARE @TableName sysname

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

-- iterate over all tables found    
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Stmt NVARCHAR(999)

    -- construct T-SQL statement to move table to 'abc' schema
    SET @Stmt = 'ALTER SCHEMA abc TRANSFER xyz.' + @TableName
    EXEC (@Stmt)

    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Ewold

why do we need a cursor? this can be done using a single-line T-SQL statement using inbuilt stored procedure. – Progestin

@ViswanathanIyer: yes - with an undocumented procedure that is not guaranteed to be there in the next release of SQL Server. Cursors will still be there - for sure. Also: this approach can move those tables from the xyz and leave anything else alone (as requested) - can you "one line of T-SQL" do the same? – Ewold

@ViswanathanIyer, Here mssqltips.com/sqlservertip/2201/… Aaron Bertrant reported about breaks of sp_MSforeachtable. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. – Julius

@ViswanathanIyer Sorry, the right link is mssqltips.com/tip.asp?tip=2201 – Julius

@ViswanathanIyer - That procedure uses a cursor anyway. – Ridgeling

Answer 3

I'm assuming You've already created the schema abc in the database.

If not you can refer here

http://www.youtube.com/watch?v=_DDgv8uek6M

http://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_database_schemas.cfm

To change the schema of all the tables in database you can use following system created msforeachtable stored procedure to rename schema of each table with alter schema.

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

Progestin

What if the database has tables in the xyz schema, but also in a def schema, and the OP wants to move only those tables from the xyz to abc ? – Ewold

Answer 4

Without using the undocumented/unsupported sp_MSforeachtable procedure, here's a somewhat concise way to select and/or run all of the necessary ALTER statements for every table on the given schema:

declare @oldSchema nvarchar(50) = 'abc'  -- usually 'dbo'
declare @newSchema nvarchar(50) = 'xyz'  -- use your new schema name

declare @sql nvarchar(max) = 
(select
  (select N'alter schema [' + @newSchema + '] transfer [' + @oldSchema + '].[' + name + ']
' as 'data()'
   from sys.tables
   where schema_name(schema_id) = @oldSchema for xml path(''), type)
 .value('text()[1]','nvarchar(max)'))

-- You can select out the results for scrutiny
select @sql

-- Or you can execute the results directly
exec (@sql)

This avoids using a cursor, and uses brackets to escape table names that may conflict with SQL keywords.

Watts