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
create table ##tt
/* insert */ into ##tt values ('test data') */
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
select @i = 1
select @rowcount = len(@sp_text)
declare @comment_count int
select @comment_count = 0
select @sp_no_comment = ''
while @i <= @rowcount
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
select @i = @i + 1
/* drop table #tbl_sp_no_comments */
create table #tbl_sp_no_comments ( sp_text varchar(8000))
while len(@sp_no_comment) >0
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))