sql - 是否有获取给定存储过程参数默认值的解决方案？

Question

我用 INFORMATION_SCHEMA.PARAMETERS用于立即获取有关存储过程参数的信息。我需要知道参数的默认值。是否有获取给定存储过程参数默认值的解决方案？

Answer 1

如果您通过 SQL 命令解析 SQL 代码...
该信息未存储在系统表中。来自 sys.parameters (在您期望的地方)、has_default_value 和 default_value 列，我们被告知要解析 SQL:

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.

If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.

证明:

CREATE PROC dbo.Paramtest (@foo int = 42)
AS
SET NOCOUNT ON;
GO

SELECT OBJECT_NAME(object_id), has_default_value, default_value
FROM sys.parameters
WHERE name = '@foo' AND object_id = OBJECT_ID('dbo.Paramtest')

-- gives Paramtest, 0, NULL