使用SYS或INFORMATION_SCHEMA表发现SQL Server过程默认参数 [英] Discover SQL Server procedure default parameters using SYS or INFORMATION_SCHEMA tables

问题描述

类似于Oracle，SQL Server在存储过程中支持参数默认值. Oracle语法:

Like Oracle, SQL Server supports parameter defaults in stored procedures. Oracle syntax:

CREATE OR REPLACE PROCEDURE p_default (
  p_in_number   IN  number   := 0,
  p_out_number  OUT number,
  p_in_varchar  IN  varchar2 := '0',
  p_out_varchar OUT varchar2,
  p_in_date     IN  date     := date '1981-07-10',
  p_out_date    OUT date
)

SQL Server语法:

SQL Server syntax:

CREATE PROCEDURE p_default (
  @p_in_number       INTEGER = 0,
  @p_out_number      INTEGER OUT,
  @p_in_varchar      VARCHAR(10) = '0',
  @p_out_varchar     VARCHAR(10) OUT,
  @p_in_date         DATE = '1981-07-10',
  @p_out_date        DATE OUT
)

借助Oracle，我可以使用以下查询发现默认值:

With Oracle, I can discover defaults using this query:

SELECT argument_name, defaulted FROM all_arguments WHERE object_id = :proc_id

如何在SQL Server的sys或INFORMATION_SCHEMA表中进行选择?我没有在INFORMATION_SCHEMA.PARAMETERS中看到任何有用的列，并且sys.parameters.has_default_value似乎没有正确设置(！)

How can I discover this in SQL Server selecting from sys or INFORMATION_SCHEMA tables? I don't see any useful column in INFORMATION_SCHEMA.PARAMETERS, and the sys.parameters.has_default_value seems not to be set correctly (!)

注意，我曾经问过类似的关于DB2的问题:

Note, I have asked as similar question about DB2:

使用SYSCAT表发现DB2过程缺省参数

推荐答案

没有简单的方法可以做到这一点.由于has_default_value 的文档指出:

There's no simple way to do it. As the documentation for has_default_value states:

SQL Server仅在此目录视图中维护CLR对象的默认值；默认值为0.因此，对于Transact-SQL对象，此列的值为0.要查看Transact-SQL对象中参数的默认值，请查询sys.sql_modules目录视图的definition列，或使用OBJECT_DEFINITION系统功能.

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.

因此，您必须提取整个存储的proc定义，然后自己对其进行解析以确定值.

So you'd have to pull the whole stored proc definition out and parse it yourself to determine the value.

旁注:我会警惕@p_in_date DATE = '1981-07-10'.我知道date数据类型比datetime更为明智，但是我不确定上述内容是否仍然模棱两可-当然，如果将其转换为datetime，则可能会导致7月10日或10月7日，具体取决于语言设置.我会更习惯'19810710'，它将始终被解释为7月10日.

Side note: I'd be wary of @p_in_date DATE = '1981-07-10'. I know that the date datatype is a bit more sensible than datetime, but I'm not sure if the above is still ambiguous - certainly if it was converted to datetime, it may result in 10th July or 7th October, depending on language settings. I'd be more comfortable with '19810710' which will always be interpreted as 10th July.

这篇关于使用SYS或INFORMATION_SCHEMA表发现SQL Server过程默认参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！