Just Code‎ > ‎

SQL Wiki - Automatic wiki markup generator for stored procedures

posted Jun 1, 2011, 7:00 AM by Peter Henell
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
Comments