SQL SERVER 2005 - CHANGER LE SCHEMA DES OBJETS D'UNE BASE DE DONNÉES

USE [MaBase]
GO

DECLARE @OldSchema		AS VARCHAR(50)
DECLARE @NewSchema	AS VARCHAR(50)
DECLARE @DBName		AS VARCHAR(50)
DECLARE @SQLCMD		AS VARCHAR(2000)	


SET @DBName = 'MaBase'
SET @OldSchema = 'AncienSchema'
SET @NewSchema = 'NouveauSchema'


--------------------------------------------------------------------------
DECLARE CURSORCHANGESCHEMA CURSOR FOR SELECT 'USE ['+ @DBName +'] ALTER SCHEMA ['+ @NewSchema +']  TRANSFER ['+ SCH.name +'].['+ OBJ.name +']' AS COMPLETE_NAME 
		FROM sys.objects OBJ INNER JOIN sys.schemas SCH ON SCH.schema_id = OBJ.schema_id WHERE (OBJ.type = 'P' OR OBJ.type = 'FN') AND SCH.name = 'serveur' 
		UNION ALL SELECT 'USE ['+ @DBName +'] ALTER SCHEMA ['+ @NewSchema +']  TRANSFER ['+ TAB.TABLE_SCHEMA +'].['+ TAB.TABLE_NAME +']' AS COMPLETE_NAME 
		FROM INFORMATION_SCHEMA.TABLES TAB WHERE TAB.TABLE_SCHEMA = @OldSchema

OPEN CURSORCHANGESCHEMA
FETCH NEXT FROM CURSORCHANGESCHEMA INTO @SQLCMD
WHILE (@@fetch_status >= 0) 
	BEGIN 
		PRINT 'MODIFICATION : '+ @SQLCMD
		EXECUTE(@SQLCMD)
		FETCH NEXT FROM CURSORCHANGESCHEMA INTO @SQLCMD
	END
CLOSE CURSORCHANGESCHEMA
DEALLOCATE CURSORCHANGESCHEMA

--------------------------------------------------------------------------