How to Determine the input and output values of stored procedures?

Question

I have hundreds of SPs and I would like to find out:

input parameters with type
output fields with type (not output parameters)

Of course I could manually go through each one and write it down but where is the fun in that...No, literally where IS the fun in that :)

Can this be done or does Sql Management Studio 2008 R2 have this capability already?

I do not even know where to start so any answer is acceptable.

Edit for enhancment of question: If we look at it as 2 different tasks, could we accomplish this easier. Even using reflection on the client side. (A quick and dirty console app would suffice.)

score 4 · Answer 1 · answered Feb 24 '12 at 19:57

You should be able to do that using the system view INFORMATION_SCHEMA.PARAMETERS. You'll have there what you need.

It "returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information."

PS: if the answer to Martin's question is "columns in the result set", then ignore my answer, it is only for the parameters of the procedures, not for any result.

Aaron Bertrand · Answer 2 · 2020-10-03T07:04:41.173

For input and output parameters, you can look at sys.parameters, sys.procedures and sys.types. Here is a procedure with various input/output parameters, alias types and even a TVP (2008+ only):

USE [tempdb];
GO
CREATE TYPE dbo.TVPType AS TABLE(id INT);
GO
CREATE PROCEDURE dbo.foobar
    @a INT,
    @b SYSNAME = N'foo',
    @c DATETIME = NULL,
    @d dbo.TVPType READONLY,
    @e NVARCHAR(MAX),
    @f INT OUTPUT,
    @g INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
SELECT @f = COUNT(*) FROM sys.objects;

END
GO

And here is a query to pull this information from sys.parameters, sys.procedures and sys.types:

SELECT 
    s = QUOTENAME(OBJECT_SCHEMA_NAME(p.[object_id])),
    o = QUOTENAME(p.name),
    p = pr.name, 
    t1.name,
    pr.max_length, 
    pr.[precision], 
    pr.scale, 
    pr.is_output,
    pr.is_readonly
FROM sys.procedures AS p
INNER JOIN sys.parameters AS pr
ON p.[object_id] = pr.[object_id]
LEFT OUTER JOIN sys.types AS t1 
ON (pr.system_type_id = t1.system_type_id)
AND (pr.system_type_id <> t1.system_type_id
OR pr.user_type_id = t1.user_type_id)
WHERE p.[object_id] = OBJECT_ID(N'dbo.foobar')
ORDER BY pr.parameter_id;

Results:

s       o           p   name    max_length  [precision]  scale  is_output  is_readonly
-----   ----------  --- -------- ---------  -----------  -----  ---------  -----------
[dbo]   [foobar]    @a  int              4           10      0          0            0
[dbo]   [foobar]    @b  sysname        256            0      0          0            0
[dbo]   [foobar]    @c  datetime         8           23      3          0            0
[dbo]   [foobar]    @d  TVPType         -1            0      0          0            1
[dbo]   [foobar]    @e  nvarchar        -1            0      0          0            0
[dbo]   [foobar]    @f  int              4           10      0          1            0
[dbo]   [foobar]    @g  int              4           10      0          1            0

Obviously you need to do some massaging to get those values to look like real data types. Cut a nvarchar's max_length in half, for example, unless it's -1, in which case it needs to get swapped out with MAX. For decimal, numeric etc., add the precision and scale appropriately. If you are using TVPs you probably want to grab the schema using SCHEMA_NAME for those types. Etc. etc.

Unfortunately, there is no way except brute force parsing the procedure body (and even that is not trivial without serious ninja RegEx skills) to determine if the parameter is nullable, or whether it has a default value, and what the default value is. This information is simply not stored in the metadata - while there are has_default_value and default_value columns in sys.parameters, they are only ever non-NULL or non-zero for CLR procedures. I've been complaining about this since 2006, but we have yet to see any advancements in this area. I did start a project called ParamParser that uses PowerShell to parse procedure and function bodies for their parameters, data types, and default values; feel free to try it out. Here's the output for the above procedure:

For result sets, Martin is quite right - there isn't a robust way to figure out what a result will consist of except using SET FMTONLY ON. There are some peculiar bugs with this one, so I would stay away from it. A popular kludge is to use OPENQUERY with a "loopback" linked server, select the results into a #temp table, but this only works if the procedure has exactly one resultset. A good example exists in one of Martin's previous answers:

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Once you've put the output into the #temp table, you can execute:

EXEC tempdb.sys.sp_help N'#tablename';

This will output, among other things, a list of column names and their data types. While this will be a tedious process to perform for hundreds of stored procedures, I do have ideas about how to automate it so if this seems like an interesting solution to you please let me know and I can expand my answer.

Martin also alluded to new functionality in SQL Server 2012 that will make this a lot easier (but it is still limited to the first resultset of a stored procedure). You can see more details about that in my answer to the same question above.

For return values, I don't know of any way to get those from the metadata (and they shouldn't be used for data anyway).

How to Determine the input and output values of stored procedures?

2 Answers2