List all stored procedures which return datasets

  • Is there any way to do that? I need to list that kind of procedures in a list on a application form. There is more than 1500 procedures in the DB, most of which are returning result but not dataset.

    Is it at all possible?

    Thank you

  • So, you want to return a list of procedures that are SELECT queries with result sets, but not INSERT, UPDATE, DELETE queries?

    Honestly, I'm not sure how I'd go about doing that. Since a given procedure could both INSERT and SELECT in pretty much any combination, there's not a simple way to make this determination. There's nothing that simply marks a procedure as SELECT only.

    You can use the monitoring tools like Query Store or Extended Events to see what's happening in the system, although they don't show the result sets being returned.

    What is it exactly that you want this information for?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • One way would be to use a test environment (so you don't break any production data) and run all of the stored procedures and see which ones give a data set as a result.  Not ideal, but SQL has no magic way to know if a stored procedure will return a data set or not.  And trying to do it by reading the code or searching for keywords won't work either as it MAY have a SELECT INTO or INSERT INTO SELECT for example.  Both of these will contain the "SELECT" keyword, but aren't returning a data set.

    And to make it more fun, it could be that stored procedure A calls stored procedure B and B gives a data set as a result but A doesn't directly.  And some stored procedures can give multiple data sets as a result.  This can be useful in applications and is a method I have used in the past (single stored procedure returns 3 data sets to the application).

    Now, if you have a good naming convention with your stored procedures, you MAY be able to use that.  For example, where I work, we use _RO as a suffix on stored procedures that are READ ONLY.  The only good reason to have a read-only stored procedure would be to return a data set (you could also do it for data sanity checking and return a variable to indicate success or failure or whatever, but we generally bake sanity checking into the stored procedure).  We use RW to indicate read/write (returns a data set AND changes some data), WO for write only (changes data, no data set returned).  So, if we wanted to see all of the stored procedures that return a data set, we would look for %_RO or %_RW in the procedure name.  You COULD do %_R%, but this runs the problem of a stored procedure like "Update_Requirements_WO" where "_R" shows up but it is not a read-based stored procedure.

    Alternately, you may be able to filter down those 1500 stored procedures by looking at where they are used.  SSRS (power BI, Tableau, Excel, any reporting solution) would return a data set for example.

    But like Grant said, there is not going to be any easy, automated, reliable way that I can think of to get this information just from the query text.

  • Hello Alex

    You might get somewhere by examining first result sets using dm views, see  sys.dm_exec_describe_first_result_set_for_object.  Read the documentation carefully though.  There are a pile of limitations including CLR, Dynamic SQL, triggers and recursion.  Be warned that I have found this approach problematic in the past.   However, your requirement is simpler so it could be worth a try.

    Adding a filter on 'column_ordinal = 1' just picks out the first column, so each 'acceptable' procedure with a dataset just returns one row.

    USE AdventureWorks2019; 
    GO

    SELECT p.name as procedure_name, r.*
    FROM sys.procedures AS p
    CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
    where r.column_ordinal = 1;
    GO

    Screenshot First DataSet

    All the best.

  • Actually your query gets as close as it could to the "solution". I already found a query using same approach that you used with dm_exec_describe_first_result_set. Just a little change in your query  - instead of filtering on column_ordinal, filter shlould be set on name column requiring it being not null. That is as close as possible to what i needed.

    Thank you, Bredon, I think the question is closed

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply