USE [master]
GO
IF OBJECT_ID('dbo.sp_MoveSchema') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_MoveSchema AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_MoveSchema
@OldSchema SYSNAME = NULL,
@NewSchema SYSNAME = NULL,
@DropOldSchema BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@UseTransaction BIT = 1,
@PrintSQL BIT = 1,
@ExecuteSQL BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT @OldSchema = LTRIM(RTRIM(PARSENAME(@OldSchema, 1))),
@NewSchema = LTRIM(RTRIM(PARSENAME(@NewSchema, 1)))
IF ISNULL(@OldSchema, '') = ''
OR ISNULL(@NewSchema, '') = ''
OR @OldSchema = @NewSchema
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_MoveSchema',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
SELECT @SQL = CASE WHEN @UseTransaction = 1 THEN 'BEGIN TRANSACTION
BEGIN TRY
' ELSE ''
END + ISNULL(Util.dbo.StringConcat('ALTER SCHEMA ' + QUOTENAME(@NewSchema) + ' TRANSFER ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(o.NAME) + ';', '
'), '') + CASE WHEN @DropOldSchema = 1 THEN '
DROP SCHEMA ' + QUOTENAME(s.NAME) + ';'
ELSE ''
END + CASE WHEN @UseTransaction = 1 THEN '
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ErrorNumber = ERROR_NUMBER(),
ErrorSeverity = ERROR_SEVERITY(),
ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(),
ErrorLine = ERROR_LINE(),
ErrorMessage = ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH' ELSE ''
END
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE o.is_ms_shipped = 0
AND ot.CreateDefinition IN ('TRIGGER', 'TABLE', 'SYNONYM', 'VIEW', 'FUNCTION', 'PROCEDURE')
AND s.name = @OldSchema
GROUP BY s.name
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
AND @SQL <> ''
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject
sp_MoveSchema
GO