Find which parameters of stored procedures are nullable (optional)

Question

I know I can use the following query to find all stored procedures and their parameters :

SELECT 
    r.*, p.*
FROM
    INFORMATION_SCHEMA.ROUTINES AS r INNER JOIN
    INFORMATION_SCHEMA.PARAMETERS AS p 
    ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE     (r.ROUTINE_TYPE = N'PROCEDURE')

From this I can see (almost) all info about parameters, but I don't know how to find which of them are nullable or have default values.

Is there any way to find this?

Thank you

Answer 1

All parameters are nullable. There is no syntax to specify that NULL should not be passed.

To find out those that have defaults you can inspect sys.parameters for CLR stored procedures

SELECT has_default_value,name
FROM sys.parameters
where object_id=object_id('YourProc')

Unfortunately this column is not currently populated correctly for TSQL stored procedures and the only ways involve parsing the object definition.