MSSQL Schema remapping stored procedure

 

MSSQL Schema remapping stored procedure

Use the below stored procedure script for remapping schema 

CREATE PROCEDURE SP_REMAP_SCHEMA(@SCHEMANAME VARCHAR(20))

AS

BEGIN

      DECLARE @currentSchemaName nvarchar(200), @tableNamenvarchar(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;

Execute procedure,

EXEC SP_REMAP_SCHEMA 'dbo';

 

Original post : http://shankarjdelphi.blogspot.com/2016/02/mssql-schema-remapping-stored-procedure.html