Schema: How do I show the parameters for a function or stored procedure?

Let's say you have the following procedure: 
 

CREATE PROCEDURE dbo.fakeProcedure      @foo INT = 5,      @bar DATETIME = GETDATE,      @dec DECIMAL(19,2),      @x TEXT,      @vc VARCHAR(255) OUTPUT  AS  BEGIN      DECLARE @ret INT      IF @foo > 10          SET @ret = 10 - @foo      ELSE          SET @ret = 10 + @foo      RETURN @ret  END  GO

 
Now, you're planning on calling this stored procedure in your ASP code or in an application, and you forget the parameter names and/or their datatypes. 
 
Well, the first thing you could do is look at the second resultset of sp_help: 
 

EXEC sp_help 'fakeProcedure'

 
However, this resultset does not differentiate between INPUT / OUTPUT parameters, nor does it show default values. 
 
There are a couple of built-in stored procedures that go into much greater detail about each parameter: 
 

EXEC sp_sproc_columns 'fakeProcedure'    --or the undocumented/unsupported:    EXEC sp_procedure_params_rowset 'fakeProcedure'

 
These give far more information than necessary, and still don't show default values for applicable params. 
 
You can also look at the syscolumns table: 
 

SELECT * FROM syscolumns      WHERE id = OBJECT_ID('fakeProcedure')

 
Again, default values are not returned. 
 
Finally, you could create your own procedure using the PARAMETERS view under INFORMATION_SCHEMA, to give you a much more concise result: 
 

CREATE PROCEDURE dbo.listProcParams      @proc_name SYSNAME  AS  BEGIN      SELECT           [Param] = COALESCE(PARAMETER_NAME, '<no params>'),          [DataType] = COALESCE(UPPER(DATA_TYPE) + CASE              WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN                   '(' + CAST(NUMERIC_PRECISION AS VARCHAR)                   + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'               WHEN RIGHT(DATA_TYPE, 4) = 'CHAR' THEN                  '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'              ELSE '' END + CASE PARAMETER_MODE               WHEN 'INOUT' THEN ' OUTPUT' ELSE ' ' END, '-')      FROM           INFORMATION_SCHEMA.PARAMETERS      WHERE          SPECIFIC_NAME = @proc_name      ORDER BY           ORDINAL_POSITION  END  GO    EXEC dbo.listProcParams 'fakeProcedure'

 
Result: 
 

Param  DataType  ------ ----------------------  @foo   INT   @bar   DATETIME   @dec   DECIMAL(19, 2)   @x     TEXT   @vc    VARCHAR(255) OUTPUT

 
Now, you'll notice if you run the following... 
 

SELECT *      FROM INFORMATION_SCHEMA.PARAMETERS      WHERE SPECIFIC_NAME='fakeProcedure'

 
...that you *still* can't determine the default values. The sad truth is that these are simply not stored by SQL Server, they are merely evaluated by the text of the procedure at runtime. So, to determine default values, you're going to have to visually inspect the actual SP code, using sp_helptext or the ROUTINES view under INFORMATION_SCHEMA: 
 

EXEC sp_helptext 'fakeProcedure'    --or    SELECT ROUTINE_DEFINITION      FROM INFORMATION_SCHEMA.ROUTINES      WHERE ROUTINE_NAME = 'fakeProcedure'

 
Please let us know if you have any tricks for determining default values programmatically (well, aside from brute force parsing, which is probably more work than resigning yourself to just look up the code for the rest of your life <G>).