Post date: Oct 8, 2012 2:18:03 PM
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 typeWITH 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 ALLSELECT '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 TRYBEGIN 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 ALLSELECT 'select * from #errors where error_level < 50000'