--视图、存储过程、函数名称
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 @OldText=@OldText + 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;
