How to revalidate/refresh all stored procedures and functions

After a stored procedure is created, you might make changes to the underlying objects that could break the stored procedure. The script presented will retrieve the list of stored procedures and functions and then will re-validate them, based on sp_refreshsqlmodule system stored procedure.

Here is the script that performs this.

SET NOCOUNT ON;

DECLARE
        @RowIndex AS SMALLINT = 1
        , @ObjectName AS NVARCHAR(128)
        , @Sql AS NVARCHAR(MAX)
       

-- Get list of objects (stored procedures, functions) to be refreshed
DECLARE @ObjectsTable AS Table (RowIndex SMALLINT IDENTITY(1,1), ObjectType nvarchar(60), ObjectName NVARCHAR(128), IsRefreshed BIT, Error NVARCHAR(MAX))
INSERT INTO @ObjectsTable (ObjectType, ObjectName, IsRefreshed)
SELECT O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON O.object_id = M.object_id
WHERE
        O.is_ms_shipped = 0
        AND O.type_desc NOT IN ('VIEW')
ORDER BY O.type_desc, O.name

-- For each function/sp...
WHILE @RowIndex <= (SELECT COUNT(*) FROM @ObjectsTable)
BEGIN  
        SELECT @ObjectName = ObjectName        
        FROM @ObjectsTable
        WHERE RowIndex = @RowIndex
       
        SET @Sql = 'EXEC sp_refreshsqlmodule ''' + @ObjectName + ''''
        BEGIN TRY
                EXEC sp_executesql @Sql
        END TRY
        BEGIN CATCH
                UPDATE @ObjectsTable
                SET
                        IsRefreshed = 0,
                        Error = ERROR_MESSAGE()
                WHERE RowIndex = @RowIndex
               
                IF @@TRANCOUNT > 0
                        ROLLBACK TRAN
        END CATCH
                                                               
        -- Process next object
        SET @RowIndex = @RowIndex + 1
END


-- List all objects not refreshed
SELECT ObjectType, ObjectName, IsRefreshed, Error
FROM @ObjectsTable
WHERE IsRefreshed = 0
GO

Is this useful?