Auto-Create Call SP TSQL Templates

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