Copying SQL Databases, renaming is not as easy as it could be. Luckily the following script can make the effort much easier. Give it a try.

After copying a SQL Server database I needed to create a new schema and transfer all tables and views and stored procedures from the old schema to the newly created schema. Using the following script made this task much easier.

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)

SET @OldSchema = 'dbo'
SET @NewSchema = 'StackOverflow'

DECLARE @sql AS varchar(MAX)

DECLARE @Schema AS varchar(MAX)
DECLARE @Obj AS varchar(MAX)

-- First transfer Tables and Views

DECLARE CU_OBJS CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + @OldSchema + '].[' + @Obj + ']'
PRINT @sql
--  EXEC (@sql)

    FETCH NEXT FROM CU_OBJS
    INTO @Schema, @Obj
END

CLOSE CU_OBJS
DEALLOCATE CU_OBJS


-- Now transfer Stored Procedures

DECLARE CU_OBJS CURSOR FOR
SELECT sys.schemas.name, sys.procedures.name
FROM sys.procedures,sys.schemas
WHERE sys.procedures.schema_id=sys.schemas.schema_id and sys.schemas.name = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj