更新时间:2022-12-05 11:34:23
It looks that Microsoft has neglected this topic and there is no trivial way to find parameters default values and even if a default value is present or not on a specific parameter:
众所周知,T-SQL存储过程的参数默认值不是 存储在sys.parameters,all_parameters和system_parameters中.他们 也不会通过sp_sproc_columns,sys.columns公开,或者 sp_procedure_params_rowset.
As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, all_parameters, and system_parameters. They are also not exposed through sp_sproc_columns, sys.columns, or sp_procedure_params_rowset.
Microsoft的反馈:
Feedback from Microsoft:
BOL Tibor Karaszi发表的文章指出仅SQL Server 在此目录视图中维护CLR对象的默认值; 因此,对于Transact-SQL对象,此列的值为0.到 查看Transact-SQL对象中参数的默认值,查询 sys.sql_modules目录视图的定义列,或使用 OBJECT_DEFINITION系统功能."
As posted by Tibor Karaszi, BOL document that "SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function."
我们甚至不存储指示参数为默认值的位 育空地区的价值.
We dont store even the bit that indicating parameter is of default value in Yukon.
我已经在此答案中测试了第一个代码段 ,它似乎适用于您的简单示例:
I have tested the first code snippet in this answer and it seems to work for your simple example:
SELECT
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
, rtoken = REVERSE(
SUBSTRING(ptoken
, CHARINDEX('=', ptoken, 1) + 1
, LEN(data2.ptoken))
)
FROM (
SELECT
data.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
, 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.[type]
, tokens = REVERSE(SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens)))
FROM (
SELECT
sm.[object_id]
, o.[type]
, 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 '
AND s.name + '.' + o.name = 'dbo.Sample1'
) 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
WHERE data2.ptoken LIKE '%=%'
) data3
但是,对于一项希望从系统视图轻松查询的任务而言,这确实很丑陋.
However, it is really ugly for a task that one expects to be easily queryable from system views.