查找存储过程的哪些参数可以为空（可选）

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

谢谢

2 个解决方案

#1

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

所有参数都可以为空。没有语法指定不应传递NULL。

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

要找出具有默认值的那些，您可以检查sys.parameters以获取CLR存储过程

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.

遗憾的是，此列目前尚未正确填充TSQL存储过程，并且唯一的方法是解析对象定义。

#2

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

#1