Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
1  
What do you mean "output fields"? Are you talking about output parameters or the columns in the result set(s)? –  Martin Smith Feb 24 '12 at 19:55
    
What is the difference? Can I have both at the same time as well? All I really care is what type of data I will be getting back. I am new to the terminology. I thought columns are also known as fields. But as Marian states, I meant "columns in the result set". :) –  Michael Wells Feb 24 '12 at 20:11
1  
columns and fields are pretty synonymous. Reason for the question was because you explicitly mentioned input parameters so wasn't sure whether you had meant to refer to output parameters there. There isn't currently a good way of getting the column list (and there may be more than one result set or its shape can vary according to procedural code in the proc). Visual Studio does it by SET FMTONLY ON; and executing the stored procedure. SQL Server 2012 has some additional functions that will help here. –  Martin Smith Feb 24 '12 at 20:13
    
What are output parameters? –  Michael Wells Feb 24 '12 at 20:18
1  
Parameters decorated with the output keyword that can be assigned to in the proc and the value retrieved by the stored procedure caller. Useful when you want to pass back scalar values rather than rows of data. They are actually input/output parameters in that they can also be used to pass values into the procedure. –  Martin Smith Feb 24 '12 at 20:22
show 2 more comments

2 Answers

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.

share|improve this answer
add comment

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('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 (though I did post a workaround on that Connect item that I won't reproduce here because it makes several unreasonable assumptions about the coding style of your stored procedures). And there is nothing at all that even gives any hope to determine whether the parameter is optional (e.g. @c and @g above).

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..sp_help '#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.

share|improve this answer
    
Thank you for writing and explaining things. Not knowing what result sests are, is it a dataset with multiple datatables? –  Michael Wells Feb 28 '12 at 18:13
    
The result set from the SELECT query up top? Did you run it (just changing the name of the procedure to search for)? It's a single resultset containing a row for per parameter (it will only become a "datatable" if you consume it in .NET and make it so). –  Aaron Bertrand Feb 28 '12 at 18:16
    
All I really need is a quick way to initially document our SPs and determine if any are returning duplicate dataset/scalars. I do not believe that we have only a few that return multiple datatables in a dataset. I already have exported the inputparameters to our SPs and functions to an excel spreadsheet but have not yet got the resulting datasets. I do like the idea of automating that you mentioned and I have no problem of massaging data. I was thinking about writing a program to get the list of SP and functions and passing each into a query to generate a list of input parameters and using –  Michael Wells Feb 28 '12 at 18:20
    
reflection on the resulting dataset to get the column names and data types. I think I might just try out your SQL query to generate the output and then massage it on a second pass. –  Michael Wells Feb 28 '12 at 18:22
    
Ah, I see now. Sorry, but as I suggested, there really isn't a way to detect duplicates in the result sets without knowing the range of input values for the procedures and running them. –  Aaron Bertrand Feb 28 '12 at 18:31
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.