SQL SERVER 2005 - CHANGER LE SCHEMA DES OBJETS DANS LES OBJETS APPELANT

USE [sccEnc]
GO

DECLARE @SQLCMD		AS NVARCHAR(4000)
DECLARE @SQL				AS NVARCHAR(max)
DECLARE @OldSchema		AS VARCHAR(200)
DECLARE @NewSchema		AS VARCHAR(200)
DECLARE @IDOBJECT AS INT
DECLARE @NUMBERLINE AS INT

SET @OldSchema = 'serveur'
SET @NewSchema = 'dbo'

DECLARE CURSORCHANGESCHEMA CURSOR FOR SELECT DISTINCT CMT.id FROM sys.syscomments CMT INNER JOIN sys.sysobjects OBJ ON CMT.id = OBJ.id
				WHERE (OBJ.type = 'V' OR OBJ.type = 'P' OR OBJ.type='FN') AND NOT(OBJ.name LIKE 'dt_%') AND NOT(OBJ.name LIKE 'sp_%')
OPEN CURSORCHANGESCHEMA
FETCH NEXT FROM CURSORCHANGESCHEMA INTO @IDOBJECT
WHILE (@@fetch_status >= 0) 
	BEGIN 

		SET @SQL = ''
		SELECT @NUMBERLINE = COUNT(CMT.id) FROM  sys.syscomments CMT WHERE CMT.id = @IDOBJECT
		PRINT CAST(@NUMBERLINE AS VARCHAR(5)) +' - '+ CAST(@IDOBJECT AS VARCHAR(10))

		IF (@NUMBERLINE > 1)
			BEGIN 
				DECLARE CURSORSP CURSOR FOR SELECT CMT.text FROM sys.syscomments CMT WHERE CMT.id = @IDOBJECT ORDER BY colid ASC
				OPEN CURSORSP
				FETCH NEXT FROM CURSORSP INTO @SQLCMD
				WHILE (@@fetch_status >= 0) 
					BEGIN 
						SET @SQL =  @SQL + CAST(@SQLCMD AS NVARCHAR(4000))
						FETCH NEXT FROM CURSORSP INTO @SQLCMD
					END
				CLOSE CURSORSP
				DEALLOCATE CURSORSP
			END
		ELSE
			BEGIN
				SELECT @SQL = CMT.text FROM sys.syscomments CMT WHERE CMT.id = @IDOBJECT
			END

		SET @SQL = RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(@SQL, 'CREATE', 'ALTER'), @OldSchema +'.', @NewSchema +'.'), '['+ @OldSchema +'].', '['+ @NewSchema +'].')))

		SET @SQL = '----------------------------------------------------------------'+ char(13) + '-- Modification effectuée le : '+ CONVERT(VARCHAR(100), getdate()) + char(13) + '----------------------------------------------------------------'+ char(10) + char(13) + @SQL
		SET @SQL = @SQL + char(10) + char(13) +'----------------------------------------------------------------'
		BEGIN TRY
			EXECUTE(@SQL)
			PRINT 'REQUETE REUSSIE : '+ char(13) + @SQL
		END TRY
		BEGIN CATCH
			PRINT 'ERREUR ('+ ERROR_MESSAGE() +') DANS LA REQUETE : '+ char(13) + @SQL
		END CATCH

		FETCH NEXT FROM CURSORCHANGESCHEMA INTO @IDOBJECT
	END
CLOSE CURSORCHANGESCHEMA
DEALLOCATE CURSORCHANGESCHEMA