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

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

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_NAMEName of the parameter.

ORDINAL_POSITION Ordinal position of the parameter.

PARAMETER_TYPE If the parameter is an OUTPUT parameter or not.

PARAMETER_HASDEFAULTIf the parameter has a default value.

PARAMETER_DEFAULTThe default value of the parameter.

IS_NULLABLEIf the parameter accepts NULL values.

DATA_TYPEData type of the parameter.

CHARACTER_MAXIMUM_LENGTHThe maximum length of the parameter's data.

CHARACTER_OCTET_LENGTHThe maximum octect length of the parameter's data.

NUMERIC_PRECISIONThe precision of the parameter.

NUMERIC_SCALEThe scale of the parameter.

DESCRIPTIONDescription of the parameter. Always NULL.

TYPE_NAMEActual name for the data type.

LOCAL_TYPE_NAMELocal name for the data type, if the data type is a user-defined data type.

|||

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?

|||

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.