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.
- October 13, 2020
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