Extract all SQL commands called in SQL Server stored procedure

user_11647371 0
Viewed 65

Let's say there is a stored procedure:

------
/****** This is my procedure
Dev
author
build date
last modified date
**********/

-- procedure starts
create procedure [temp_my_proc]
begin
    declare var1, var2, .. var N

    -- 1st select 
    select a, b, c, d 
    from mytable_1;

    -- delete record 
    delete from mytable_2;

    -- insert record select 
    insert into mytable_4
        select x, y, z 
        from mytable_3;
end

Now I want to extract the SQL used in this stored procedure by using some SQL command. I tried using these commands:

create table #tmp 
(
     id int identity(1, 1),
     sqlsyntax varchar(max)
);

insert into #tmp (sqlsyntax) 
    exec sp_helptext temp_my_proc;

select * 
from #tmp;

But select * from #tmp is spitting out entire definition of the stored procedure in separate rows. How I can extract just the 3 SQL statements from the stored procedure by omitting all multiple line or single line comment present in the stored procedure?

Expected results is

select a, b, c, d 
from mytable_1;

delete from mytable_2;

insert into mytable_4
    select x, y, z 
    from mytable_3;
stored-procedures sql-server
1 Answers

For modern versions of SQL Server STRING_SPLIT() is the one you need. You need to cross apply your text output against string split, splitting on the enter character i.e.

SELECT #tmp.id, lines.value
FROM #tmp
CROSS APPLY STRING_SPLIT(#tql.sqlsyntax,'
') AS lines

That will split your results into one row per line. Unfortunately you would not be able to distinguish multi-line statements from single ones.

Then you can filter by out the comments by checking

WHERE lines.value NOT LIKE '%--%' AND lines.value<> ''

However, some of your comments are enclosed by /* signs, which go across lines. Unfortunately, there is nothing you can do directly. But if you manually replaced the contents of those comments with -- or used a regex to do so you would be able to handle it.

user_154115230
Related