SQL Wiki - Automatic wiki markup generator for stored procedures

Post date: Jun 1, 2011 2:00:47 PM

SELECT WikiMarkup FROM sys.objects so CROSS APPLY ( SELECT ( 'h2. ' + SCHEMA_NAME(so.schema_id) + '.' + so.Name + '\\' + ISNULL( (SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty( 'Description' , 'SCHEMA', SCHEMA_NAME(so.schema_id) ,'PROCEDURE', so.Name , NULL, NULL)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}') ) AS WikiMarkup UNION ALL SELECT CASE WHEN EXISTS (SELECT 1 FROM fn_listextendedProperty( 'Description' ,'SCHEMA', SCHEMA_NAME(so.schema_id) ,'PROCEDURE', so.Name ,'PARAMETER', NULL)) THEN '||Parameter Name|Datatype|Default value?|Is Output column|Description|' ELSE 'No params' END UNION ALL SELECT '|' + p.name + '|' + --(,Scale) CASE WHEN system_type_id IN (231,167,175,239) THEN CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(max_length AS NVARCHAR(50)) + ')' WHEN system_type_id = 106 THEN CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(PRECISION AS NVARCHAR(50)) + ', ' + CAST(Scale AS NVARCHAR(50)) + ')' ELSE TYPE_NAME(p.system_type_id) END + '|' + CAST(ISNULL(p.default_value, 'N/a') AS NVARCHAR(100)) + '|' + CASE p.is_output WHEN 1 THEN 'Yes' ELSE 'No' END + '|' + ISNULL(CAST(VALUE AS NVARCHAR(MAX)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}') + '|' FROM sys.parameters p CROSS APPLY fn_listextendedProperty( 'Description' ,'SCHEMA', SCHEMA_NAME(so.schema_id) ,'PROCEDURE', so.Name ,'PARAMETER', p.name) WHERE p.object_id = so.object_id UNION ALL SELECT ' \\\ ' ) spInfo WHERE so.type IN ('p', 'fn', 'if', 'tf')ORDER BY so.name