Tuesday, November 4, 2008

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

 

3 comments:

Anonymous,  March 12, 2010 at 9:34 PM  

Nice brief and this mail helped me alot in my college assignement. Thank you on your information.

Anonymous,  March 24, 2010 at 3:12 AM  

Well your article helped me terribly much in my college assignment. Hats afar to you send, intention look audacious for the duration of more cognate articles promptly as its anecdote of my pick question to read.

Anonymous,  September 9, 2010 at 1:20 PM  

Good code but what should I do if I want to preserve the comments. I wanted to reverse engineer from the db all the functions and stored proc but sp_helptext strips out all the comments.