Remove comments from SQL Server Stored Procedure.

 

Comments have been a part of SQL Server procedures. If you work in ERP applications or other huge applications, then comments are even more important. They are kept for multiple purposes like to mark author, dates, causes, implications. But often, huge comments become so disgusting that, it becomes difficult to study the stored procedures. In multiple thousand line stored procedures in huge applications, it is also likely that working code is quite little while comment portion is large. I have tried here to create a stored procedure which removes comment portion from a given stored procedure.

 

The procedure works for if the length of procedue is less than 8000. Also, I have considered only multiline comment marks for developing this procedue. That means, I have considered that no single line comment in the procedure exists. The single line comment can also be removed by a single loop. I will write about that in my next post.

 

First I created a test procedure with name sp_Proc1.

Here is the code.

 

create proc sp_proc1   
as   
begin   
 create table ##tt   
 (   
  col1 varchar(200)   
 )   

/*
/*

/* insert */ into ##tt values ('test data')  */
*/

select * 
from ##tt  
end   

 

Then I created the procedure to remove comments.

This procedue just uses sp_helptext , a built function, to get the text of given input stored procedure with comments.

It puts that in a temporary table and then a single variable @sp_text. 

Then it loops through each character of that variable and removes the content between opening comment mark '/*' and closing comment mark '*./'. The procedure also works for comments inside comments. It also takes care of line returns by using custom string ('\n') to represent new line.

The code goes like this.

 

 

 /* drop table #tbl_sp_text */
create table #tbl_sp_text ( id int identity(1,1), sp_text varchar(8000))

delete from #tbl_sp_text
insert into #tbl_sp_text
exec sp_helptext  sp_proc1 /* procedure name */

declare @sp_text varchar(8000), @sp_text_row varchar(8000), @sp_no_comment varchar(8000)
declare @c char(1)
declare @i int, @rowcount int
set @sp_text = ''

select @sp_text = @sp_text + case when len(@sp_text) > 0 then '\n' else '' end + sp_text
from #tbl_sp_text

select  @i  = 1
select @rowcount = len(@sp_text)
declare @comment_count int
select @comment_count  = 0
select @sp_no_comment = ''

while @i <= @rowcount
begin
 if substring(@sp_text,@i,2) = '/*'
  select @comment_count = @comment_count + 1
 else if substring(@sp_text,@i,2) = '*/'  
  select @comment_count = @comment_count - 1  
 else if @comment_count = 0
  select @sp_no_comment = @sp_no_comment + substring(@sp_text,@i,1)

 if substring(@sp_text,@i,2) = '*/' 
  select @i = @i + 2
 else
  select @i = @i + 1
end

/* drop table #tbl_sp_no_comments */
create table #tbl_sp_no_comments ( sp_text varchar(8000))

while len(@sp_no_comment) >0
begin
 insert into #tbl_sp_no_comments
 select substring( @sp_no_comment, 0, charindex('\n', @sp_no_comment))
 select @sp_no_comment = substring(@sp_no_comment, charindex('\n',@sp_no_comment) + 2, len(@sp_no_comment))
end

select *
from #tbl_sp_no_comments