Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Auto-Create Call SP TSQL Templates

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested

Credits:

Author: Unknown
Date:    10 Jun 2020

Description

For all Stored Procedures in a database, generate TSQL templates to invoke each SP

Code



SELECT '/* '+qualifiedName+' */' +CHAR(13)+CHAR(10)+REPLACE( REPLACE( REPLACE( REPLACE( STUFF(ExecuteScript, /*delete final comma line-terminator*/ LEN(ExecuteScript)-CHARINDEX('|,|', REVERSE(ExecuteScript)+'|')-1,3 ,'') ,'\n',CHAR(13)+CHAR(10)) ,'|,|',',') /*put in new-lines and convert token to comma*/ ,'&lt;','<') ,'&gt;','>') AS Template FROM (SELECT so.name AS Name, OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName, 'EXEC '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID)) +'.'+QUOTENAME(so.name) +COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '') +REPLACE(COALESCE( ( SELECT REPLACE ('\n '+sp.name+' = '+'''<' +REPLACE( COALESCE( CONVERT(VARCHAR(MAX),value) ,REPLACE(sp.name,'@','') ) ,',','') +','+t.name+','+'>'''+'|,|' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'''', CASE WHEN t.name IN ('char', 'varchar','nchar' ,'nvarchar','text','ntext' ,'datetime','date') THEN '''' WHEN t.precision=0 THEN '''' ELSE '' END) FROM sys.parameters sp INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID LEFT OUTER JOIN sys.extended_properties ep ON sp.object_id = ep.major_ID AND sp.parameter_ID = minor_ID WHERE sp.object_ID=so.object_ID AND parameter_ID>0 ORDER BY parameter_ID FOR XML PATH('') ),'|,|') ,',||', '') [ExecuteScript] FROM sys.objects so LEFT OUTER JOIN sys.extended_properties ep /* get any extended properties */ ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID AND minor_ID=0 WHERE OBJECTPROPERTY(object_id, 'IsProcedure')<>0)f ORDER BY Name





ċ
Andy Hughes,
Jun 10, 2020, 5:19 PM
Comments