SqlServer更新視圖預存程序函數指令碼

標籤：

--視圖、預存程序、函數名稱 DECLARE @ NAME NVARCHAR(255); --局部遊標 DECLARE @CUR CURSOR --自動修改未上狀態為曠課 SET @CUR= CURSOR SCROLL DYNAMIC FOR SELECT NAME FROM DBO.SYSOBJECTS      WHERE NAME NOT IN ( ‘SYSCONSTRAINTS‘ , ‘SYSSEGMENTS‘ )          AND          (              OBJECTPROPERTY(ID, N ‘IsView‘ ) = 1               --視圖              OR OBJECTPROPERTY(ID,N ‘IsProcedure‘ ) = 1        --預存程序              OR OBJECTPROPERTY(ID,N ‘IsScalarFunction‘ ) = 1   --純量涵式              OR OBJECTPROPERTY(ID,N ‘IsTableFunction‘ ) = 1    --標題函數              OR OBJECTPROPERTY(ID,N ‘IsInlineFunction‘ ) = 1   --內嵌函式          );  OPEN @CUR; FETCH NEXT FROM @CUR INTO @ NAME  WHILE (@@FETCH_STATUS=0) BEGIN       DECLARE @OldText NVARCHAR( MAX );      DECLARE @NewText NVARCHAR( MAX );       --讀取建立指令碼，當指令碼超長時分成多條記錄時合并      SELECT @[email protected] + CHAR (10) + CHAR (13) + RTRIM(TEXT) FROM SYSCOMMENTS WHERE ID = OBJECT_ID(@ NAME );       --將建立指令碼替換為更新指令碼      SET @NewText= REPLACE (@OldText,N ‘CREATE VIEW‘ ,N ‘ALTER VIEW‘ );      SET @NewText= REPLACE (@NewText,N ‘CREATE PROCEDURE‘ ,N ‘ALTER PROCEDURE‘ );      SET @NewText= REPLACE (@NewText,N ‘CREATE FUNCTION‘ ,N ‘ALTER FUNCTION‘ );            BEGIN TRY          EXEC (@NewText);      END TRY      BEGIN CATCH          PRINT N ‘---------------------------------------------------------------------------‘ ;          PRINT @ NAME + N ‘ : ‘ + ERROR_MESSAGE();          --PRINT @OldText;          PRINT N ‘---------------------------------------------------------------------------‘ ;      END CATCH       FETCH NEXT FROM @CUR INTO @ NAME  END  CLOSE @CUR; DEALLOCATE @CUR;

SqlServer更新視圖預存程序函數指令碼