I have created below stored procedure with default value:
CREATE PROCEDURE [dbo].[Sample1]
@OrderID INT = 10285
AS
SELECT ProductName, OrderID
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID
AND Od.OrderID = @OrderID
Tried to get default value (10285) of parameters using sys.parameters
.
Select a.object_id, a.default_value
from sys.parameters a
inner join sys.types b on b.system_type_id = a.system_type_id
where Object_id = object_id('[dbo].[Sample1]')
But I got NULL as default_value
, while I was expecting 10285
as default_value
.
Is there any way to get default value?
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:
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.
Feedback from Microsoft:
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.