For input and output parameters, you can look at
sys.types. Here is a procedure with various input/output parameters, alias types and even a TVP (2008+ only):
CREATE TYPE dbo.TVPType AS TABLE(id INT);
CREATE PROCEDURE dbo.foobar
@b SYSNAME = N'foo',
@c DATETIME = NULL,
@d dbo.TVPType READONLY,
@f INT OUTPUT,
@g INT = NULL OUTPUT
SET NOCOUNT ON;
SELECT @f = COUNT(*) FROM sys.objects;
And here is a query to pull this information from
s = QUOTENAME(OBJECT_SCHEMA_NAME(p.[object_id])),
o = QUOTENAME(p.name),
p = pr.name,
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;
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
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.
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.