Find which parameters of stored procedures are nullable (optional)

I know I can use the following query to find all stored procedures and thei开发者_运维问答r 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

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.

Try this query to get all defaul values for specific stored procedure -

SELECT  
      data3.[object_name]
    , data3.name
    , [default_value] = REVERSE(RTRIM(SUBSTRING(
            data3.rtoken
        , CASE 
            WHEN CHARINDEX(N',', data3.rtoken) > 0 
                THEN CHARINDEX(N',', data3.rtoken) + 1
            WHEN CHARINDEX(N')', data3.rtoken) > 0 
                THEN CHARINDEX(N')', data3.rtoken) + 1
            ELSE 1 
            END
        , LEN(data3.rtoken)
        )))
FROM (
    SELECT  
          data2.name
        , data2.[object_name]
        , rtoken = CASE WHEN data2.ptoken LIKE '%=%' 
            THEN REVERSE(SUBSTRING(ptoken
                        , CHARINDEX('=', ptoken, 1) + 1
                        , LEN(data2.ptoken))
                    )
            END
    FROM (
        SELECT  
              data.name
            , data.[object_name]
            , ptoken = SUBSTRING(
                    data.tokens
                , token_pos + name_length + 1
                , ISNULL(ABS(next_token_pos - token_pos - name_length - 1), LEN(data.tokens))
            )
        FROM (
            SELECT  
                  sm3.tokens
                , sm3.[object_name]
                , p.name
                , name_length = LEN(p.name)
                , token_pos = CHARINDEX(p.name, sm3.tokens)
                , next_token_pos = CHARINDEX(p2.name, sm3.tokens)
            FROM (
                SELECT 
                      sm2.[object_id]
                    , sm2.[object_name]
                    , tokens = REVERSE(SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens))) 
                FROM (
                    SELECT 
                          sm.[object_id]
                        , [object_name] = s.name + '.' + o.name
                        , tokens = REVERSE(SUBSTRING(
                                        sm.[definition]
                                    , CHARINDEX(o.name, sm.[definition]) + LEN(o.name) + 1
                                    , ABS(CHARINDEX(N'AS', sm.[definition]))
                                    )  
                        ) 
                    FROM sys.sql_modules sm WITH (NOLOCK)
                    JOIN sys.objects o WITH (NOLOCK) ON sm.[object_id] = o.[object_id]
                    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] 
                    WHERE o.[type] = 'P '
                ) sm2
                WHERE sm2.tokens LIKE '%=%'
            ) sm3
            JOIN sys.parameters p WITH (NOLOCK) ON sm3.[object_id] = p.[object_id]
            OUTER APPLY (
                SELECT p2.name
                FROM sys.parameters p2 WITH (NOLOCK) 
                WHERE p2.is_output = 0
                    AND sm3.[object_id] = p2.[object_id] 
                    AND p.parameter_id + 1 = p2.parameter_id
            ) p2
            WHERE p.is_output = 0
        ) data
    ) data2
) data3