Just Code‎ > ‎

TSQL - Execute ALL stored procedures to see if they work or not

posted Oct 8, 2012, 7:18 AM by Peter Henell   [ updated Oct 8, 2012, 7:20 AM ]
When you have alot of old stored procedures it may be hard to know which ones are working and which have been broken by schema changes etc.

This scripts will generate a script that will execute all the stored procedures in the selected schema (filtered on schema_id).

DO NOT RUN THIS IN PRODUCTION or in any environment. This will actually execute all the stored procedures and that may cause severe problems. I have included a bug to prevent this being run by accident.

I only created this script to know which of our reporting procedures was still working, ie read only!.

-- 1: execute all procedures in the schema with some default parameter values depending on parameter type

WITH paramValues(DataTyp, VALUE) AS(
    SELECT 'uniqueidentifier', 'A8ABE1E2-90B9-40F6-9A23-4160D47B275F' UNION ALL 
    SELECT 'date', '2012-10-08' UNION ALL 
    SELECT 'time', '10:14:26.033' UNION ALL 
    SELECT 'datetime2', '2012-10-08 10:14:26.033' UNION ALL 
    SELECT 'tinyint', '1' UNION ALL 
    SELECT 'smallint', '1' UNION ALL 
    SELECT 'int', '1' UNION ALL 
    SELECT 'smalldatetime', '2012-10-08' UNION ALL 
    SELECT 'datetime', '2012-10-08 10:14:26.033' UNION ALL 
    SELECT 'float', '1' UNION ALL 
    SELECT 'ntext', 'a' UNION ALL 
    SELECT 'bit', '1' UNION ALL 
    SELECT 'decimal', '1' UNION ALL 
    SELECT 'numeric', '1' UNION ALL 
    SELECT 'bigint', '1' UNION ALL 
    SELECT 'varbinary', '0x7065746572' UNION ALL 
    SELECT 'varchar', 'a' UNION ALL 
    SELECT 'char', 'a' UNION ALL 
    SELECT 'nvarchar', 'a' UNION ALL 
    SELECT 'nchar', 'a' UNION ALL 
    SELECT 'xml', '<peter>peter</peter>' UNION ALL 
    SELECT 'sysname', 'object'
)


SELECT 'IF OBJECT_ID(''tempdb..#errors'') IS NOT NULL DROP TABLE #errors; 
CREATE TABLE #errors(error_level varchar(50), message VARCHAR(max), procName VARCHAR(max));'

UNION ALL

SELECT 
    'BEGIN TRY
    
    EXEB '+schema_name(ob.schema_id)+'.' + ob.name + CASE WHEN paramAndValue IS NOT NULL THEN ' @' + STUFF( SUBSTRING(paramAndValue, 0, LEN(paramAndValue)), 1, 1, '') + ';'
                                ELSE ';'
                            END
    + '
    END TRY
BEGIN CATCH
    INSERT #errors
    SELECT ERROR_NUMBER(), Error_message(),'''+ob.name+''';
END CATCH;
'                
FROM 
    sys.objects ob
OUTER APPLY
(
    SELECT 
        pa.name + ' = ' + '''' + pv.value + '''' + ', '
    FROM
        sys.parameters pa
    INNER JOIN
        paramValues pv
        ON pv.DataTyp = TYPE_NAME(pa.system_type_id)
    WHERE 
        ob.object_id = pa.object_id
    ORDER BY pa.object_id    
    FOR XML PATH('')
        
) pa(paramAndValue)
    
WHERE 
    TYPE = 'p' AND SCHEMA_ID = SCHEMA_ID('reporting')

UNION ALL

SELECT 'select * from #errors where error_level < 50000'
Comments