判断SP参数在T-SQL中是否有默认值 [英] Determine whether SP Parameter has a Default Value in T-SQL
问题描述
是否有任何方法可以从 SQL Server 内部确定 SP 的参数是否具有默认值(我在 2012 年仅供参考)?还有其他 线程，但是这些建议似乎无法准确地为我提供此信息.
Is there any way to determine from within SQL Server (I'm on 2012 FYI) if a SP's parameters have default values? There are other threads on this, however the suggestions don't seem to get me this information accurately.
以下是我尝试过的一些方法；
Here are a couple of things I've tried;
select *
from sys.objects so join sys.parameters sp on so.object_id = sp.object_id
where so.type='P'
and so.name = 'someSp'
上面的查询返回了许多列，听起来像是我在正确的树上吠叫(其中有has_default_value，default_value)但是这些似乎并没有改变我是否有默认值在我的 SP 与否.(has_default 值始终为 0，default_value 始终为 null)
The above query returns a number of columns that sound like I'm barking up the right tree (has_default_value, default_value among them) but these don't seem to vary whether I have a default value in my SP or not. (has_default value is always 0, default_value is always null)
exec sp_sproc_columns 'someSp'
同样的交易；上面的 SP 返回了许多列，包括 NULLABLE 和 IS_NULLABLE；NULLABLE 总是等于 1 并且 IS_NULLABLE = YES，无论我的 SP 内容如何.
Same deal; the above SP returns a number of columns including NULLABLE and IS_NULLABLE; NULLABLE is always equal to 1 and IS_NULLABLE = YES, regardless of my SP contents.
注释；SQL Server 管理工作室清楚地显示与每个 SP 参数关联的元数据.
A note; SQL Server management studio clearly displays the metadata associated with a each SP Parameter.
我使用 SQL Profiler 检查在 Management Studio 的对象资源管理器中查看 SP 参数时会发生什么.展开参数文件夹时，会运行两个查询.第一个查询在这里粘贴有点长(尽管如果有帮助，我会这样做).它包含一个名为 DEFAULT VALUE 的列；但是，据我所知，它始终为 NULL.第二个查询只返回 SP 的主体；大概输出到文本编辑器窗口(尽管我担心 mgmt studio 中可能会发生一些解析！)
I've used SQL Profiler to examine what happens when I view the parameters of a SP in Management Studio's Object Explorer. When you expand the parameters folder, there are two queries run. The first query is a bit long for pasting here (though I'll do so if helpful). It contains a column called DEFAULT VALUE; however it's always NULL as far as I can tell. The second query simply returns the body of the SP; presumably to output to the text editor window (though I'm afraid there could be some parsing happening within mgmt studio!)
仅供参考/只是为了确保我不会丢失我的弹珠，我创建了两个毫无意义的 Sps 只是为了测试.它们看起来像:
For reference / just to make sure I'm not losing my marbles I've created two meaningless Sps just for testing. They look like:
CREATE PROCEDURE TestDefaultSpValue_Default
@I INT = 2
AS
BEGIN
SET NOCOUNT ON;
SELECT @I
END
CREATE PROCEDURE TestDefaultSpValue_NoDefault
@I INT
AS
BEGIN
SET NOCOUNT ON;
SELECT @I
END
推荐答案
MS SQL 仅存储 CLR 存储过程和函数的默认设置，因此在这种情况下只能解析对象定义.要运行该示例，您可以创建一个空白的存储过程，或者采用其他任何方式.
MS SQL stores default settings only for CLR stored procedures and functions, so only way in this case is parse the object definition. To run the example, you can create a blank stored procedure, or take any other.
ALTER PROCEDURE dbo.usp_test1
(
@a UNIQUEIDENTIFIER = NULL,
@b DATETIME = '20100101',
@c DATETIME = DEFAULT,
@d BIT = 1,
@e BIT,
@k INT = 1,
@f BIT = 0, @g NVARCHAR(MAX) = '23235',
@h INT = 3,
@j DECIMAL(10,2) = DEFAULT
)
WITH RECOMPILE
AS
BEGIN
PRINT 1;
END
此查询返回存储过程的默认值列表:
This query return list of default values for stored procedure:
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.usp_test1'
) 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
通过这个查询，你可以知道存储过程是否包含任何默认值:
And by this query, you can know if the stored procedure contains any default values:
DECLARE @name SYSNAME = 'dbo.usp_test1'
IF EXISTS(
SELECT 1
FROM (
SELECT
sm2.[object_id]
, tokens = SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens))
FROM (
SELECT
sm.[object_id]
, 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 = @name
) sm2
) sm3
WHERE sm3.tokens LIKE '%=%'
) PRINT @name + ' have default values'
这篇关于判断SP参数在T-SQL中是否有默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
