sql server - 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

Answer 1

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