sql-server – 确定SP参数在T-SQL中是否具有默认值

如果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'

sql-server – 确定SP参数在T-SQL中是否具有默认值

解决方法

猜你在找的MsSQL相关文章