List all stored procedures which return datasets

Question

List all stored procedures which return datasets

AlexF

SSC Enthusiast

Points: 176
More actions
February 26, 2021 at 12:08 pm

#3850536

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

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

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

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?

This reply was modified 3 years, 1 month ago by Grant Fritchey.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 2

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Bredon SSC Journeyman Points: 88 More actions · Answer 3

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.

AlexF SSC Enthusiast Points: 176 More actions · Answer 4

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