DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory.

The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.

There is no way around that. And if you want to recompile 100 sprocs you have to execute each one and each one returns data.

I didn't want this so i created this script that uses a not well know SET option called

SET FMTONLY ON

which returns only schema and no data to the client. Since returning data can take a long time this thing is pretty fast.

This script clears the plan cache and recompiles every stored procedure, function and view in the database and returns only schema and no data.

If nothing else it's a one time process that can be automated. All parameters are null, so if you want to optimize plans for special

parameter values you'll have to do it by hand.

Run this, copy/paste the results into a new query window and run it:

SELECT  'DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS;' AS statements 
UNION ALL
SELECT  'EXEC(''SET FMTONLY ON; ' + 
        CASE 
            WHEN routine_type = 'scalar_fn' THEN
                 'SELECT ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' (' +  LEFT(params, LEN(params)-1) + ')'
            WHEN routine_type = 'table_fn' THEN
                 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' (' +  LEFT(params, LEN(params)-1) + ')'
            WHEN routine_type = 'procedure' THEN
                 'EXEC ' + QUOTENAME(obj_schema) + '.' + 
                 QUOTENAME(obj_name) + ' ' + LEFT(params, LEN(params)-1)
            WHEN routine_type = 'view' THEN
                 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name)
        END + '; SET FMTONLY OFF; '')' AS statements 
FROM 
(
    SELECT  routine_type, obj_schema, obj_name, RTRIM(REPLICATE('NULL, ', COUNT(*))) AS params
    FROM    
    (
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'scalar_fn' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'FUNCTION' 
                AND parameter_mode = 'IN'
                AND routine_body = 'SQL'
                AND r.data_type <> 'TABLE'
        UNION ALL
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'procedure' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'PROCEDURE'         
               AND routine_body = 'SQL'
        UNION ALL
        SELECT  r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'table_fn' AS routine_type
        FROM    INFORMATION_SCHEMA.ROUTINES r
                JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
                                                              AND r.specific_name = p.specific_name
        WHERE   routine_type = 'FUNCTION' 
                AND parameter_mode = 'IN'
                AND routine_body = 'SQL'
                AND r.data_type = 'TABLE'
        UNION ALL
        SELECT  table_schema AS obj_schema, table_name AS obj_name, 'view' AS routine_type
        FROM    INFORMATION_SCHEMA.VIEWS
    ) t1
    GROUP BY obj_schema, obj_name, routine_type
) t2