Use the below stored procedure to remap the existing schema to
new one for MS SQL db type,
CREATE PROCEDURE
SP_REMAP_SCHEMA(@SCHEMANAME VARCHAR(20))
AS
BEGIN
DECLARE @currentSchemaName nvarchar(200), @tableName nvarchar(200);
DECLARE tableCursor CURSOR
FAST_FORWARD FOR
SELECT TABLE_SCHEMA,
TABLE_NAME
FROM information_schema.tables
UNION ALL
SELECT
TABLE_SCHEMA, TABLE_NAME
FROM information_schema.views
UNION ALL
SELECT ROUTINE_SCHEMA,
ROUTINE_NAME
FROM information_schema.ROUTINES WHERE ROUTINE_NAME <>
'SP_REMAP_SCHEMA'
ORDER BY 1, 2;
DECLARE @SQL nvarchar(200);
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO
@currentSchemaName, @tableName;
WHILE @@FETCH_STATUS
= 0
BEGIN
SET @SQL = 'ALTER SCHEMA '+
@SCHEMANAME +' TRANSFER
' + @currentSchemaName + '.' + @tableName;
PRINT @SQL;
EXEC (@SQL);
FETCH NEXT FROM tableCursor INTO
@currentSchemaName, @tableName;
END;
CLOSE tableCursor;
DEALLOCATE tableCursor;
END;
--EXEC SP_REMAP_SCHEMA 'dbo';
