How can i find the default values for a Stored Procedure parameter

Question

I have a stored procedure,

CREATE PROCEDURE [dbo].[SReport]

(

@ListByBatch BIT,

@UnPrintedOnly BIT = 1

)

AS

............................

........

I can find the information regarding parameters of this Stored Procedure from "syscolumns" or 

"Information_Schema.Parameters" system tables. These tables provides information about parametername, datatype ...etc. But dont provide information about default values for these parameters.

Here i have assigned a value, Does SQL Server stores this value????

Does SQL Server maintains information about Default Values for Parameter ? (For tables it maintains the default value for a column).

Is there any way , i can find All parameter information for a stored procedure including Default value..........????

Answer 1

Hi,
you can try sp_procedure_params_rowset

sp_procedure_params_rowset will return the following result set.

PROCEDURE_CATALOG	Name of the database containing the procedure.
PROCEDURE_SCHEMA	Owner's name of the procedure.
PROCEDURE_NAME	Name of the procedure.
PARAMETER_NAME	Name of the parameter.
ORDINAL_POSITION	Ordinal position of the parameter.
PARAMETER_TYPE	If the parameter is an OUTPUT parameter or not.
PARAMETER_HASDEFAULT	If the parameter has a default value.
PARAMETER_DEFAULT	The default value of the parameter.
IS_NULLABLE	If the parameter accepts NULL values.
DATA_TYPE	Data type of the parameter.
CHARACTER_MAXIMUM_LENGTH	The maximum length of the parameter's data.
CHARACTER_OCTET_LENGTH	The maximum octect length of the parameter's data.
NUMERIC_PRECISION	The precision of the parameter.
NUMERIC_SCALE	The scale of the parameter.
DESCRIPTION	Description of the parameter. Always NULL.
TYPE_NAME	Actual name for the data type.
LOCAL_TYPE_NAME	Local name for the data type, if the data type is a user-defined data type.

Answer 2

Hi,

Thanks for reply.

I tried this , But it shows NULL for PARAMETER_DEFAULT and 0 for PARAMETER_HASDEFAULT though i have set default value for that parameter.

Is there any other way , we can find this default value?

 

Answer 3

SQL SERVER does not store the default parameter values for transact sql stored procedure. It does so for clr stored procedures. See here

http://msdn2.microsoft.com/en-us/library/ms176074.aspx

In SQL SERVER 2005, You can use object_definition() function to get the definition of the procedure and then parse it to get the default parameter values.

In SQL 2K, the syscomments system table stores the definitions for the various stored procedures.

Answer 4

Right, you won't be able to find these.  SQL Server parses a Stored Procedure everytime it runs it (no matter if it has an Execution Plan or not).  This allows for the Default values to take effect.  However, you'll never see these in any MetaData within SQL because it would take a lot of RegEx and parsing to actually determine what the Default values are, not to mention depending on the data type things could get really weird.  So that's why they don't do it.