Extract comments from SQL code for documentation

• 
  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
  

  rhodrie April 17, 2015 06:55

  - 0 +

Add comment

Please sign in to leave a comment.