如果SP的参数具有默认值,有没有办法从sql Server(我在2012年FYI)内确定？这有
other
threads,但建议似乎没有准确地告诉我这些信息.
这是我尝试过的几件事;
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)
exec sp_sproc_columns 'someSp'
同样的交易;上面的SP返回多个列,包括NULLABLE和IS_NULLABLE;无论我的SP内容如何,NULLABLE始终等于1且IS_NULLABLE = YES.
一张纸条; sql Server管理工作室清楚地显示与每个SP参数关联的元数据.
我使用sql事件探查器来检查在Management Studio的对象资源管理器中查看SP的参数时会发生什么.展开参数文件夹时,会运行两个查询.第一个查询在这里粘贴有点长(虽然如果有帮助我会这么做).它包含一个名为DEFAULT VALUE的列;但据我所知,它总是为NULL.第二个查询只返回SP的主体;大概是输出到文本编辑器窗口(虽然我担心在mgmt studio中可能会发生一些解析！)
作为参考/只是为了确保我没有丢失我的弹珠我已经创建了两个无意义的Sps仅用于测试.他们看着像是：
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存储过程和函数存储默认设置,因此在这种情况下只能解析对象定义.
若要运行该示例,您可以创建一个空白存储过程,或采取任何其他过程.
若要运行该示例,您可以创建一个空白存储过程,或采取任何其他过程.
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
此查询返回存储过程的默认值列表：
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
通过此查询,您可以知道存储过程是否包含任何默认值：
DECLARE @name SYSNAME = 'dbo.usp_test1'
IF EXISTS(
SELECT 1
FROM (
SELECT
sm2.[object_id],tokens = SUBSTRING(sm2.tokens,LEN(sm2.tokens))
FROM (
SELECT
sm.[object_id],tokens = REVERSE(SUBSTRING(
sm.[definition],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'
