Below is the SQL that can be used to change the database schema of all tables in an SQL Server Database. We use it when restoring databases from Production to CRP or Development.
USE JDE_CRP_NEW --TODO: Change this to DB Name
SET NOCOUNT ON
DECLARE @NewSchema nvarchar(256)
DECLARE @OldSchema nvarchar(256)
Set @NewSchema = 'CRPDTA'; --TODO: Change to New Schema
Set @OldSchema = 'PRODDTA'; --TODO: Change to Old Schema
--Don’t need to change anything below this
DECLARE @TableName nvarchar(256)
DECLARE TableName CURSOR FOR
SELECT DISTINCT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @OldSchema
OPEN TableName
FETCH TableName INTO @TableName
WHILE @@Fetch_Status = 0
BEGIN
Print 'Modifying ' + @TableName
EXEC('ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName)
FETCH TableName INTO @TableName
END
CLOSE TableName
DEALLOCATE TableName
