如何确定SQL Server存储过程参数是否具有默认值?

我找到了一种使用SMO的方法:

Server srv; 
srv = new Server("ServerName"); 

Database db; 
db = srv.Databases["MyDatabase"]; 

var Params = db.StoredProcedures["MyStoredProc"].Parameters;

foreach(StoredProcedureParameter param in Params) {
    Console.WriteLine(param.Name + "-" + param.DefaultValue);
}

SELECT 
    pa.NAME, 
    t.name 'Type',
    pa.max_length,
    pa.has_default_value,
    pa.default_value
FROM 
    sys.parameters pa
INNER JOIN 
    sys.procedures pr ON pa.object_id = pr.object_id
INNER JOIN 
    sys.types t ON pa.system_type_id = t.system_type_id
WHERE 
        pr.Name = 'YourStoredProcName'

不幸的是,尽管这看起来像小菜一碟,但它不起作用:-(

来自Technet:

只保留默认值 在这个目录中 观点;因此,此列具有 Transact-SQL对象的值为0 . Transact-SQL对象中的参数, sys.sql_模块目录视图,或使用 对象定义系统函数。

因此,您所能做的就是查询 sys.sql_modules 还是打电话 SELECT object_definition(object_id) 烂透了!!

看来真的没有别的办法了。。。我感到惊讶和震惊。。。。。

可能在SQL Server 2008 R2中?:-) 马克

这是PowerShell中SMO的答案:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "MyServer\MyInstance"
$db = $srv.Databases["MyDatabase"];
$proc = $db.StoredProcedures["MyStoredProcedure"]

foreach($parameter in $proc.Parameters) {
  if ($parameter.DefaultValue){
     Write-Host "$proc ,  $parameter , $($parameter.DefaultValue)"
  }
  else{
     Write-Host "$proc ,  $parameter , No Default Value"
  }
 }

运行内置的 sp_help 存储过程?

这是一种黑客行为,但您始终可以为可选参数指定一个特殊名称,如:

@年龄可选=15

…然后编写一个简单的方法,检查参数是否为可选参数。这并不理想,但考虑到目前的情况,这实际上可能是一个不错的解决方案。

我就是这么做的。获取存储过程的一部分,从第一个参数开始,一直到AS语句。使用declare语句创建了一个临时存储过程,并返回所有参数ID、名称、列类型(如果它们具有默认值)及其值。然后执行存储过程,假设它们具有默认值的参数之间是否有等号,如果它们没有默认值,我在执行过程中将null传递给参数,然后读取结果集,或者如果存在,则存储过程填充一个临时表,以便稍后查询它。我检查了参数之间是否有任何等号,如果是,最初我假设它们有默认值。如果有一个带有等号的注释等,则表示该过程没有默认值,并且在执行过程中,我没有传递任何参数,执行失败,我捕获了错误消息,读取了参数名称,并执行了该过程。这次,我将null传递给参数。在这个过程中,我使用了一个CLR字符串concat函数,因此,如果直接执行,它将不会编译,但您可能可以用XML路径等替换,或者给我发电子邮件,如果您愿意,我可以引导您通过CLR。因为我联合了所有参数,所以我将它们转换为varchar(max)

USE Util
GO
CREATE AGGREGATE [dbo].[StringConcat]
(@Value nvarchar(MAX), @Delimiter nvarchar(100))
RETURNS nvarchar(MAX)
EXTERNAL NAME [UtilClr].[UtilClr.Concat]
GO
CREATE FUNCTION dbo.GetColumnType (@TypeName SYSNAME,
                                  @MaxLength SMALLINT,
                                  @Precision TINYINT,
                                  @Scale TINYINT,
                                  @Collation SYSNAME,
                                  @DBCollation SYSNAME)
RETURNS TABLE
    AS
RETURN
    SELECT  CAST(CASE WHEN @TypeName IN ('char', 'varchar')
                      THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                  ELSE CAST(@MaxLength AS VARCHAR)
                                             END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
                                                              ELSE ''
                                                         END
                      WHEN @TypeName IN ('nchar', 'nvarchar')
                      THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                  ELSE CAST(@MaxLength / 2 AS VARCHAR)
                                             END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
                                                              ELSE ''
                                                         END
                      WHEN @TypeName IN ('binary', 'varbinary') THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                                                            ELSE CAST(@MaxLength AS VARCHAR)
                                                                                       END + ')'
                      WHEN @TypeName IN ('bigint', 'int', 'smallint', 'tinyint') THEN @TypeName
                      WHEN @TypeName IN ('datetime2', 'time', 'datetimeoffset') THEN @TypeName + '(' + CAST (@Scale AS VARCHAR) + ')'
                      WHEN @TypeName IN ('numeric', 'decimal') THEN @TypeName + '(' + CAST(@Precision AS VARCHAR) + ', ' + CAST(@Scale AS VARCHAR) + ')'
                      ELSE @TypeName
                 END AS VARCHAR(256)) AS ColumnType
GO
go
USE [master]
GO
IF OBJECT_ID('dbo.sp_ParamDefault') IS NULL 
    EXEC('CREATE PROCEDURE dbo.sp_ParamDefault AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_ParamDefault
    @ProcName SYSNAME = NULL OUTPUT
AS 
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SQL VARCHAR(MAX),
    @ObjectId INT = OBJECT_ID(LTRIM(RTRIM(@ProcName))),
    @FirstParam VARCHAR(256),
    @LastParam VARCHAR(256),
    @SelValues VARCHAR(MAX),
    @ExecString VARCHAR(MAX),
    @WhiteSpace VARCHAR(10) = '[' + CHAR(10) + CHAR(13) + CHAR(9) + CHAR(32) + ']',
    @TableExists BIT = ABS(SIGN(ISNULL(OBJECT_ID('tempdb..#sp_ParamDefault'), 0))),
    @DeclareSQL VARCHAR(MAX),
    @ErrorId INT,
    @ErrorStr VARCHAR(MAX)

IF @ObjectId IS NULL 
    BEGIN
        SET @ProcName = NULL
        PRINT '/* -- SILENCE OPERATION --
IF OBJECT_ID(''tempdb..#sp_ParamDefault'') IS NOT NULL DROP TABLE #sp_ParamDefault
CREATE TABLE #sp_ParamDefault (Id INT, NAME VARCHAR(256), TYPE VARCHAR(256), HasDefault BIT, IsOutput BIT, VALUE VARCHAR(MAX))
*/

EXEC dbo.sp_ParamDefault
    @ProcName = NULL
'
RETURN
    END

SELECT  @SQL = definition,
        @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)),
        @FirstParam = FirstParam,
        @LastParam = LastParam
FROM    sys.all_sql_modules m (NOLOCK)
CROSS APPLY (SELECT MAX(CASE WHEN p.parameter_id = 1 THEN p.name
                        END) AS FirstParam,
                    Util.dbo.StringConcat(p.name, '%') AS Params
             FROM   sys.parameters p (NOLOCK)
             WHERE  p.object_id = m.OBJECT_ID) p
CROSS APPLY (SELECT TOP 1
                    p.NAME AS LastParam
             FROM   sys.parameters p (NOLOCK)
             WHERE  p.object_id = m.OBJECT_ID
             ORDER BY parameter_id DESC) l
WHERE   m.object_id = @ObjectId
IF @FirstParam IS NULL 
    BEGIN
        IF @TableExists = 0 
            SELECT  CAST(NULL AS INT) AS Id,
                    CAST(NULL AS VARCHAR(256)) AS Name,
                    CAST(NULL AS VARCHAR(256)) AS Type,
                    CAST(NULL AS BIT) AS HasDefault,
                    CAST(NULL AS VARCHAR(MAX)) AS VALUE
            WHERE   1 = 2
        RETURN
    END

SELECT  @DeclareSQL = SUBSTRING(@SQL, 1, lst + AsFnd + 2) + '
'
FROM    (SELECT PATINDEX ('%' + @WhiteSpace + @LastParam + @WhiteSpace + '%', @SQL) AS Lst) l
CROSS APPLY (SELECT SUBSTRING (@SQL, lst, LEN (@SQL)) AS SQL2) s2
CROSS APPLY (SELECT PATINDEX ('%' + @WhiteSpace + 'AS' + @WhiteSpace + '%', SQL2)  AS AsFnd) af


DECLARE @ParamTable TABLE (Id INT NOT NULL,
                           NAME SYSNAME NULL,
                           TYPE VARCHAR(256) NULL,
                           HasDefault BIGINT NULL,
                           IsOutput BIT NOT NULL,
                           TypeName SYSNAME NOT NULL) ;
WITH    pr
          AS (SELECT    p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName,
                        p.Parameter_id,
                        t.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,
                        ct.ColumnType,
                        MAX(Parameter_id) OVER (PARTITION BY (SELECT 0)) AS MaxParam,
                        p.is_output
              FROM      sys.parameters p (NOLOCK)
              INNER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
              INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
              CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.precision, p.scale, db.collation_name, db.collation_name) ct
              WHERE     OBJECT_ID = @ObjectId)
    INSERT  @ParamTable
            (Id,
             NAME,
             TYPE,
             HasDefault,
             IsOutput,
             TypeName)
            SELECT  Parameter_id AS Id,
                    ParameterName AS NAME,
                    ColumnType AS TYPE,
                    HasDefault,
                    is_output AS IsOutput,
                    TypeName
            FROM    pr a
            CROSS APPLY (SELECT ISNULL('%' + (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b WHERE b.parameter_id < a.parameter_id), '') + '%'
                                + ParameterName + '%=' + '%' + CASE WHEN parameter_id = MaxParam THEN @WhiteSpace + 'AS' + @WhiteSpace + '%'
                                                                    ELSE (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b
                                                                                    WHERE b.parameter_id > a.parameter_id) + '%'
                                                               END AS ptt) b
            CROSS APPLY (SELECT SIGN (PATINDEX (ptt, @DeclareSQL)) AS HasDefault) hd

AGAIN:
SELECT  @SelValues = CASE WHEN @TableExists = 1 THEN 'INSERT #sp_ParamDefault(Id, Name, Type, HasDefault, IsOutput, Value)
'                         ELSE ''
                     END + 'SELECT * FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(Id AS VARCHAR) + ', ''' + Name + ''', ''' + Type + ''', '
                                                                           + CAST(HasDefault AS VARCHAR) + ', ' + CAST(IsOutput AS VARCHAR) + ', '
                                                                           + CASE WHEN TypeName NOT LIKE '%char%' THEN 'CAST(' + name + ' AS VARCHAR(MAX))'
                                                                                  ELSE name
                                                                             END + ')', ',
') + '
) d(Id, Name, Type, HasDefault, IsOutput, Value)',
        @ExecString = 'EXEC #sp_ParamDefaultProc
' + ISNULL(Util.dbo.StringConcat(CASE WHEN HasDefault = 0 THEN Name + ' = NULL'
                                 END, ',
'), '')
FROM    @ParamTable

SET @SQL = 'CREATE PROCEDURE #sp_ParamDefaultProc
' + SUBSTRING(@DeclareSQL, CHARINDEX(@FirstParam, @DeclareSQL), LEN(@DeclareSQL)) + '
' + @SelValues

IF OBJECT_ID('TEMPDB..#sp_ParamDefaultProc') IS NOT NULL 
    DROP PROCEDURE #sp_ParamDefaultProc
EXEC(@SQL)

BEGIN TRY
    EXEC(@ExecString)
END TRY
BEGIN CATCH
    SELECT  @ErrorStr = ERROR_MESSAGE(),
            @ErrorId = ERROR_NUMBER()
-- there must have been a comment containing equal sign between parameters
    UPDATE  p
    SET     HasDefault = 0
    FROM    (SELECT PATINDEX ('%expects parameter ''@%', @ErrorStr) AS ii) i
    CROSS APPLY (SELECT CHARINDEX ('''', @ErrorStr, ii + 20) AS uu) u
    INNER JOIN @ParamTable p ON p.name = SUBSTRING(@ErrorStr, ii + 19, uu - ii - 19)
    WHERE   ii > 0

    IF @@ROWCOUNT > 0 
        GOTO AGAIN

    RAISERROR(@ErrorStr, 16, 1)
    RETURN 30
END CATCH
GO
EXEC sys.sp_MS_marksystemobject 
    sp_ParamDefault
GO

use the T-SQL parser 微软提供的。

在两个集合中。这个 Microsoft.Data.Schema.ScriptDom 包含提供程序不可知类和 这个 Microsoft.Data.Schema.ScriptDom.Sql 程序集包含用于 语法分析器和脚本生成器 特定于SQL Server。

如何具体地使用它来识别参数以及它们是否为默认值还没有涉及,这将是您必须使用示例代码来解决的问题(可能需要付出大量的努力)。