Removing comments from your T-SQL query can be daunting task, especially if the query is long, and has multiple inline comment sections and blocks.
This procedure will strip all the comments from your T-SQL query and return only the parts, that are uncommented.
Procedure is able to detect and remove the following type of comments (regardless of the position of the code or comment):
- in-line comment ( — comments )
- multiline comment (Slash star /* comments)
- in-line comment block
- comment within a comment (multiline block)
For example, a query like :
CREATE OR ALTER PROCEDURE [dbo].[sql_sample_procedure]
AS
BEGIN
-- Query
/*********************
This is our Query
With author, date and place
*******************/
---- Adding some comments
SELECT
s.Name
,s.Surname
-- ,s.Surname
,d.DepartmentName -- Comment there
-- Comment here
-- /*,d.DepartmentID*/
/* This is a comment */ -- works
---------------------
/* this is a inline comment
in two lines */
/* /* this is a double comment */*/ -- works
,'test' AS test
/* /* comment */*/ --nope
FROM Students AS s
JOIN Departments AS D
ON d.DepartmentId = s.DepartmentId
END;
GO
would be converted (stripped and all comment blocks removed) into:
CREATE PROCEDURE [dbo].[sql_sample_procedure]
AS
BEGIN
SELECT
s.Name
,s.Surname
,d.DepartmentName
,'test' AS test
FROM Students AS s
JOIN Departments AS D
ON d.DepartmentId = s.DepartmentId
END;
Let’s look into the T-SQL code for removing and stripping the comments using a procedure. We will explore two ways for stripping and removing comments; as procedure and as T-SQL query passed as an argument.
Creating the procedure for removing comments
The procedure for stripping the comments has three steps:
- Running EXEC sp_helptext to get the T-SQL query in a enumerated table
- Stripping and removing all slash star /* comments; whether in-line or multiple lines.
- Removing single line comments — comments.
DROP TABLE IF EXISTS dbo.SQL_query_table;
CREATE TABLE dbo.SQL_query_table (
id INT IDENTITY(1,1) NOT NULL
,query_txt NVARCHAR(4000)
)
INSERT INTO dbo.SQL_query_table
EXEC sp_helptext
@objname = @Procedure_name
DECLARE @proc_text varchar(8000) = ''
DECLARE @proc_text_row varchar(8000)
DECLARE @proc_no_comment varchar(8000) = ''
DECLARE @comment_count INT = 0
SELECT @proc_text = @proc_text + CASE
WHEN LEN(@proc_text) > 0 THEN '\n'
ELSE '' END + query_txt
FROM dbo.SQL_query_table
DECLARE @i INT = 1
DECLARE @rowcount INT = (SELECT LEN(@proc_text))
WHILE (@i <= @rowcount)
BEGIN
IF SUBSTRING(@proc_text,@i,2) = '/*'
BEGIN
SELECT @comment_count = @comment_count + 1
END
ELSE IF SUBSTRING(@proc_text,@i,2) = '*/'
BEGIN
SELECT @comment_count = @comment_count - 1
END
ELSE IF @comment_count = 0
SELECT @proc_no_comment = @proc_no_comment + SUBSTRING(@proc_text,@i,1)
IF SUBSTRING(@proc_text,@i,2) = '*/'
SELECT @i = @i + 2
ELSE
SELECT @i = @i + 1
END
WHILE (@i <= @rowcount)
BEGIN
IF SUBSTRING(@proc_text,@i,4) = '/*/*'
BEGIN
SELECT @comment_count = @comment_count + 2
END
ELSE IF SUBSTRING(@proc_text,@i,4) = '*/*/'
BEGIN
SELECT @comment_count = @comment_count - 2
END
ELSE IF @comment_count = 0
SELECT @proc_no_comment = @proc_no_comment + SUBSTRING(@proc_text,@i,1)
IF SUBSTRING(@proc_text,@i,4) = '*/*/'
SELECT @i = @i + 2
ELSE
SELECT @i = @i + 1
END
DROP TABLE IF EXISTS #tbl_sp_no_comments
CREATE TABLE #tbl_sp_no_comments (
rn INT IDENTITY(1,1)
,sp_text VARCHAR(8000)
)
WHILE (LEN(@proc_no_comment) > 0)
BEGIN
INSERT INTO #tbl_sp_no_comments (sp_text)
SELECT SUBSTRING( @proc_no_comment, 0, CHARINDEX('\n', @proc_no_comment))
SELECT @proc_no_comment = SUBSTRING(@proc_no_comment, CHARINDEX('\n',@proc_no_comment) + 2, LEN(@proc_no_comment))
END
DROP TABLE IF EXISTS #tbl_sp_no_comments_fin
CREATE TABLE #tbl_sp_no_comments_fin
(rn_orig INT IDENTITY(1,1)
,rn INT
,sp_text_fin VARCHAR(8000))
DECLARE @nofRows INT = (SELECT COUNT(*) FROM #tbl_sp_no_comments)
DECLARE @ii INT = 1
WHILE (@nofRows >= @ii)
BEGIN
DECLARE @LastLB INT = 0
DECLARE @Com INT = 0
SET @Com = (SELECT CHARINDEX('--', sp_text,@com) FROM #tbl_sp_no_comments WHERE rn = @ii)
SET @LastLB = (SELECT CHARINDEX(CHAR(10), sp_text, @LastLB) FROM #tbl_sp_no_comments WHERE rn = @ii)
INSERT INTO #tbl_sp_no_comments_fin (rn, sp_text_fin)
SELECT
rn
,CASE WHEN @Com = 0 THEN sp_text
WHEN @Com <> 0 THEN SUBSTRING(sp_text, 0, @Com) END as new_sp_text
FROM #tbl_sp_no_comments
WHERE
rn = @ii
SET @ii = @ii + 1
END
Once the procedure is created, all you need to do is to run the remove comments procedure:
EXEC dbo.remove_comments
@procedure_name = N'sql_sample_procedure'
And the result will be same procedure T-SQL query text with all the comments removed (or *decluttered*).
As always, code is available in at the Github in tomaztk/SQLServer-Data-Lineage: Data Lineage for Microsoft SQL Server (github.com).
Happy T-SQL Coding!
TomazTsql 
[…] Tomaz Kastrun is not pleased with these comments: […]
LikeLike
I have found the comment-removing code sometimes removes the end part of the stored procedure it’s processing. How do we submit bug reports? Do we download and edit the template “md” file and email it to you?
LikeLiked by 1 person
Thanks, appreciate upgrades!
You can fork the github repository: https://github.com/tomaztk/SQLServer-Data-Lineage
and commit an issue or update.
Best, Tomaž
LikeLiked by 1 person
This can be also implemented as CLR to call from within SQL If you like.
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Text.RegularExpressions;
private String RemoveCommentsOfSQL(bool RemoveMultiLineComment, bool RemoveSingleLineComment, bool RemoveGO, String SQL)
{
TSql150Parser mParser = new TSql150Parser(true);
Sql150ScriptGenerator gen = new Sql150ScriptGenerator();
System.Collections.Generic.IList Errors = null;
System.Collections.Generic.IList tokens = mParser.GetTokenStream(new StringReader(SQL), out Errors);
if (Errors.Count() > 0)
{
StringBuilder estr = new StringBuilder();
foreach (var er in Errors)
{
estr.AppendLine(er.Message);
}
throw new Exception(estr.ToString(), new Exception());
}
StringBuilder SQLOut = new StringBuilder(“”);
foreach (TSqlParserToken token in tokens)
{
if (token.Text != null && token.Text.Length > 0)
{
if (token.TokenType == TSqlTokenType.MultilineComment)
{
if (!RemoveMultiLineComment)
{
SQLOut.Append(token.Text);
}
}
else if (token.TokenType == TSqlTokenType.SingleLineComment)
{
if (!RemoveSingleLineComment)
{
SQLOut.Append(token.Text);
}
}
else if (token.TokenType == TSqlTokenType.Go)
{
if (!RemoveGO)
{
SQLOut.Append(token.Text);
}
}
else { SQLOut.Append(token.Text); }
}
}
return TrimMultilineLocal(SQLOut.ToString());
}
static string TrimMultilineLocal(string Input)
{
return _LeadingAndTrailingWhiteSpace.Replace(_SpaceBeforeNewLine.Replace(Input, NewLine), “”);
}
LikeLiked by 1 person
Thank you Gokhan Varol for your CLR solution. Besides that, there are also possibilities to do it with R, Python or Java using external procedures but initially, I wanted to use only T-SQL.
LikeLiked by 1 person
That post was not a replacement to that was posted prior.
Microsoft.SqlServer.TransactSql.ScriptDom library can be used for any kind of sql needs imagined, ex I used it for parsing stored procedure default parameters from CLR, generating create script even though the sql.all_sql_modules text is still pointing to an older name after sp_rename, injecting output clause to update/insert/delete and so on. Also it is surprisingly quick against very large text’s and so on
LikeLiked by 1 person
I wrote a query that removes all /* comment blocks */ and — comment lines from object_definitions using this trickery:
with
no_comment_blocks as
(
select object_id, string_agg(iif(e > 0, substring(txt, e+2, len(txt)), txt),”) txt
from (select object_id, CHARINDEX(‘*/’, value) e, value as txt
from sys.objects
cross apply string_split(REPLACE(OBJECT_DEFINITION(object_id),’/*’,char(1)),char(1), 1)
where type in (‘FN’,’P’,’TF’,’V’)) a
group by object_id
)
,no_comment_lines as
(
select object_id, string_agg(iif(e > 0, substring(txt, e, len(txt)), txt),”) txt
from (select object_id, CHARINDEX(char(13)+char(10), value) e, value as txt
from no_comment_blocks
cross apply string_split(REPLACE(txt,’–‘,char(2)),char(2), 1)) a
group by object_id
)
select object_id, txt
from no_comment_lines
LikeLiked by 1 person
sorry those – long dashes should be — double dashes
LikeLike
Thank you.
I love your update and I would like to welcome you to fork this repository: https://github.com/tomaztk/SQLServer-Data-Lineage where the larger set of code is available.
LikeLike
Thank you Glen, super appreciate it.
LikeLike