CREATE OR ALTER PROCEDURE dbo.TSQL_data_lineage /* Author: Tomaz Kastrun Date: August 2022 GitHub: github.com/tomaztk Blogpost: Description: Removing all comments from your T-SQL Query for a given procedure for better code visibility and readability - separate function Remove all unused characters. Create data lineage for inputed T-SQL query Usage: EXEC dbo.TSQL_data_lineage @InputQuery = N' SELECT * FROM master.dbo.spt_values ' */ ( @InputQuery NVARCHAR(MAX) ) AS BEGIN /* ****************************** * * 2. Remove comments characters * ******************************** */ 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) ) -- Breaks the procedure into lines with linebreak -- INSERT INTO dbo.SQL_query_table -- EXEC sp_helptext -- @objname = @InputQuery -- Breaks the query into lines with linebreak DECLARE @MAX_nof_break INT = (select len(@InputQuery) - len(replace(@InputQuery, CHAR(10), ''))) DECLARE @start_nof_break INT = 1 declare @iq2 NVARCHAR(max) = @InputQuery declare @max_len int = (SELECT len(@InputQuery)) declare @start_pos int = 0 declare @br_pos int = 0 while (@MAX_nof_break >= @start_nof_break) BEGIN SET @br_pos = (SELECT charindex( char(10), @iq2) ) INSERT INTO dbo.SQL_query_table(query_txt) SELECT substring(@InputQuery,@start_pos, @br_pos ) SET @start_pos = @start_pos + @br_pos SET @iq2 = SUBSTRING(@InputQuery, @start_pos, @max_len) SET @start_nof_break = @start_nof_break + 1 END --- STart removing comments DECLARE @proc_text varchar(MAX) = '' DECLARE @proc_text_row varchar(MAX) DECLARE @proc_no_comment varchar(MAX) = '' 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 DROP TABLE IF EXISTS dbo.Query_results_no_comment SELECT rn ,sp_text_fin INTO dbo.Query_results_no_comment FROM #tbl_sp_no_comments_fin WHERE DATALENGTH(sp_text_fin) > 0 AND LEN(sp_text_fin) > 0 /* ****************************** * * 3. Create data lineage * ******************************** */ DECLARE @orig_q VARCHAR(MAX) SELECT @orig_q = COALESCE(@orig_q + ', ', '') + sp_text_fin FROM dbo.Query_results_no_comment order by rn asc DROP TABLE IF EXISTS dbo.LN_Query DECLARE @stmt2 NVARCHAR(MAX) SET @stmt2 = REPLACE(REPLACE(@orig_q, CHAR(13), ' '), CHAR(10), ' ') SELECT TRIM(REPLACE(value, ' ','')) as val ,dbo.fn_removelistChars(value) as val_f ,row_number() over (ORDER BY (SELECT 1)) as rn INTO dbo.LN_Query from string_split(REPLACE(@stmt2, CHAR(13), ' '), ' ' ) WHERE REPLACE(value, ' ','') <> ' ' OR REPLACE(value, ' ','') <> ' ' DECLARE @table TABLE (command_ VARCHAR(200), location_ VARCHAR(200), order_ INT) DECLARE @command_i VARCHAR(200) = '' DECLARE @next_step BIT = 0 -- FALSE (1 = TRUE) DECLARE @previous VARCHAR(200) = '' DECLARE @order INT = 1 DECLARE @previous_cmd VARCHAR(200) = '' DECLARE @previous_step BIT = 0 -- FALSE DECLARE @ttok VARCHAR(100) = '' DECLARE @i_row INT = 1 DECLARE @max_row INT = (SELECT MAX(rn) FROM dbo.LN_Query) DECLARE @row_commands_1 NVARCHAR(1000) = 'select,delete,insert,drop,create,select,truncate,exec,execute' DECLARE @row_commands_2 NVARCHAR(1000) = 'select,not,if,exists,select' DECLARE @row_commands_3 NVARCHAR(1000) = 'from,join,into,table,exists,sys.dm_exec_sql_text,sys.dm_exec_cursors,exec,execute' WHILE (@max_row >= @i_row) BEGIN DECLARE @command VARCHAR(1000) = (SELECT val FROM dbo.LN_Query WHERE rn = @i_row) IF @command IN (SELECT REPLACE(TRIM(LOWER(value)), ' ','') FROM STRING_SPLIT(@row_commands_1, ',')) BEGIN IF LOWER(@command) = 'select' BEGIN SET @command = 'select' END SET @command_i = @command END IF (@next_step = 1) BEGIN IF @command NOT IN (SELECT REPLACE(TRIM(LOWER(value)), ' ',' ') FROM STRING_SPLIT(@row_commands_2,',')) BEGIN IF (LOWER(@previous) = 'into') SET @command_i = 'select into' IF (@command NOT LIKE '%#%' OR @command NOT LIKE '%#%') SET @ttok = ' ' + @command + ' as (' IF (@ttok NOT IN (SELECT @stmt2)) INSERT INTO @table (command_, location_, order_) SELECT @command_i ,@command ,@order SET @command_i = @command_i END SET @next_step = 0 IF @command IN ('sys.dm_exec_sql_text','sys.dm_exec_cursors') BEGIN SET @next_step = 1 END END IF (@command IN (SELECT REPLACE(TRIM(LOWER(value)), ' ','') FROM STRING_SPLIT(@row_commands_3,','))) BEGIN SET @next_step = 1 END SET @previous_cmd = @command_i SET @previous = @command SET @i_row = @i_row + 1 END DROP TABLE IF EXISTS dbo.final_result -- Final results SELECT * ,row_number() over (order by (select 1)) as rn INTO dbo.final_result FROM @table SELECT [command_] AS Clause_name ,[location_] AS Object_Name ,rn AS order_DL FROM dbo.final_result END; GO