Extract comments from SQL code for documentation

Bechir

Hi,

I need to extract comments from SQL code to create documentation for my stored procedures.

Is there any feature that enables SQL Prompt to automatically extract comments from SQL code and create documentation for it?

Thanks,
Bechir

rhodrie

This script pulls stored procedure and function header comments
starting with

@procedure name

and ending with

-----

into the MS_Description extended property used by SQL Doc.

DECLARE fred CURSOR FORWARD_ONLY FOR 
SELECT s2.name, s2.type, s3.name
,SUBSTRING(definition,
	CHARINDEX(CHAR(13),definition,CHARINDEX('@procedure name',definition,1)),
	CHARINDEX('-----',definition,CHARINDEX('@procedure name',definition,1))-CHARINDEX('@procedure name',definition,1))
FROM sys.sql_modules s1
JOIN sys.objects s2 ON s1.object_id = s2.object_id
LEFT JOIN sys.schemas s3 ON s2.schema_id = s3.schema_id
WHERE s1.definition LIKE '%@procedure name%'
AND s2.type IN ('IF','FN','P')
ORDER BY s2.type, s2.name

DECLARE @objname VARCHAR(MAX), @type VARCHAR(MAX), @schemaname VARCHAR(MAX), @desc VARCHAR(MAX), @sql VARCHAR(MAX), @systype VARCHAR(MAX)

OPEN fred

FETCH NEXT FROM fred INTO @objname , @type , @schemaname , @desc 

WHILE @@FETCH_STATUS=0 
BEGIN


SET @systype =	CASE @type 
				WHEN 'IF' THEN 'FUNCTION'
				WHEN 'FN' THEN 'FUNCTION'
				WHEN 'P' THEN 'PROCEDURE'
				END 
				
SET @desc = REPLACE(@desc,'''','''''')


SET @objname = '['+@objname+']'

                IF EXISTS ( SELECT  1
                            FROM    sys.extended_properties
                            WHERE   major_id = OBJECT_ID('['+@schemaname+'].'+@objname)
                                    AND minor_id = 0 ) 
                    BEGIN 

                        SET @sql = 'EXEC sys.sp_dropextendedproperty
	 @name = ''MS_Description'', 
		@level0type = ''SCHEMA'', 
		@level0name = '''+@schemaname+''', 
		@level1type = '''+@systype+''', 
		@level1name = ' + @objname + '; 	
	    
		'
		
                        PRINT @sql 	
                        BEGIN TRY 	
							EXEC(@sql)
                        END TRY
                        BEGIN CATCH
                        END CATCH;
                        
                    END    
		
                SET @sql = 'EXEC sys.sp_addextendedproperty
	 @name = ''MS_Description'', 
		@value = ''' + @desc + ''', 
		@level0type = ''SCHEMA'', 
		@level0name = '''+@schemaname+''', 
		@level1type = '''+@systype+''', 
		@level1name = ' + @objname + '; 	
	    
		'
	    

                PRINT @sql
                EXEC(@sql)




FETCH NEXT FROM fred INTO @objname , @type , @schemaname , @desc 
END 

CLOSE fred
DEALLOCATE fred