开发者

Is there a solution for getting the default value of the parameters of a given stored procedure?

I use INFORMATION_SCHEMA.PARAMETERS for getting information about a stored procedure parameter now. I need to know the default values of parameters. Is there a solu开发者_C百科tion for getting the default value of the parameters of a given stored procedure?


Parse the SQL code if you are doing it via SQL commands...

The information isn't stored in a system table. From sys.parameters (where you'd expect it), has_default_value and default_value columns, we're told to parse SQL:

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.

If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.

To prove:

CREATE PROC dbo.Paramtest (@foo int = 42)
AS
SET NOCOUNT ON;
GO

SELECT OBJECT_NAME(object_id), has_default_value, default_value
FROM sys.parameters
WHERE name = '@foo' AND object_id = OBJECT_ID('dbo.Paramtest')

-- gives Paramtest, 0, NULL


Declare @pSProcName NVARCHAR(MAX)='ProcedureName'

DECLARE @SQLTEXT NVARCHAR(MAX),@start int ,@end int,@SearchCode NVARCHAR(MAX)
SELECT @SQLTEXT =OBJECT_DEFINITION(OBJECT_ID(@pSProcName))
SELECT @start=CHARINDEX('@',@SQLTEXT,1)
SELECT @end =min(val) FROM (
SELECT PATINDEX('%'+CHAR(10)+'AS'+CHAR(10)+'%',@SQLTEXT ) AS val
UNION ALL SELECT PATINDEX('%'+CHAR(10)+'AS'+CHAR(13)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(13)+'AS'+CHAR(10)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(13)+'AS'+CHAR(13)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(10)+'AS'+CHAR(32)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(32)+'AS'+CHAR(10)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(32)+'AS'+CHAR(32)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(13)+'AS'+CHAR(32)+'%',@SQLTEXT )
UNION ALL SELECT PATINDEX('%'+CHAR(32)+'AS'+CHAR(13)+'%',@SQLTEXT )
) S 
Where s.val <> 0

SELECT @SearchCode=SUBSTRING(@sqltext,@start,@end - @start)
SELECT S2.parameter_id,S2.ParameterName,S2.DataType,S1.Default_Value FROM
(
SELECT CASE WHEN Data like '%=%' then RIGHT(Data,len(Data)-CHARINDEX('=',Data,1)) ELSE '' END as Default_Value, 
CASE WHEN Data like '%=%' then 1 ELSE 0 END as Has_default_Value
,Data FROM(
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Data  
 FROM  
 (
     SELECT  CAST ('<M>' + REPLACE(@SearchCode, ',', '</M><M>') + '</M>' AS XML) AS Data  

 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))s
 )S1 

INNER JOIN
(
Select p.parameter_id,p.name as ParameterName,UPPER(t.name) AS DataType from sys.all_parameters p
Inner JOIN sys.types t
on t.user_type_id = p.user_type_id
where Object_name(OBJECT_ID) = @pSProcName
) S2
ON S1.Data LIKE '%'+S2.ParameterName+'%'+S2.DataType+'%'


@N.Dinesh.Reddy has an amazing answer. I re-worked it a little bit to provide support for new-lines between parameter definitions, as well as supporting parameters whose default values containing XML characters (e.g. @Parameter1 NVARCHAR(MAX) = N'<').

DECLARE @ProcedureName NVARCHAR(MAX) = N'dbo.TestProcedure';


DECLARE @ObjectId INT = OBJECT_ID(@ProcedureName);
DECLARE @DefinitionText NVARCHAR(MAX) = REPLACE(REPLACE(OBJECT_DEFINITION(@ObjectId), CHAR(10), N' '), CHAR(13), N' ');
DECLARE @FirstParameterIndex INT = CHARINDEX('@',@DefinitionText, 1);

-- Pull out only the parameters, and xml-encode them.
SET @DefinitionText = (SELECT SUBSTRING(@DefinitionText, @FirstParameterIndex, PATINDEX(N'% AS %', @DefinitionText) - @FirstParameterIndex) FOR XML PATH(N''));

-- Find the parameter names.
SELECT b.parameter_id, b.name, b.system_type_id, b.user_type_id, b.max_length, b.is_output, a.has_default_value FROM (
    SELECT LEFT(ParameterDefinition, CHARINDEX(N' ', ParameterDefinition, 1)) parameter_name, CAST(CASE WHEN EqualSignIndex = 0 THEN 0 ELSE 1 END AS BIT) has_default_value FROM (
        SELECT ParameterDefinition, CHARINDEX(N'=', ParameterDefinition, 1) EqualSignIndex FROM (
            SELECT LTRIM(RTRIM(Split.ParameterDefinition.value(N'.', N'NVARCHAR(100)'))) ParameterDefinition FROM (
                SELECT CAST(CONCAT('<a>', REPLACE(@DefinitionText, ',', '</a><a>'), '</a>') AS XML) Xml
            ) a CROSS APPLY Xml.nodes('/a') AS Split(ParameterDefinition)
        ) a
    ) a
) a
FULL JOIN sys.all_parameters b ON a.parameter_name = b.name
WHERE b.object_id = @ObjectId;


I poked around the dissassembly of SQL Server Management Studio to find out how Microsoft themselves do it, because I wanted to ensure my approach was fully-correct.

As the other posters have surmised, and to my surprise (read: appalled shock) SSMS runs a RegEx against sys.sql_modules.definition to extract parameter information that isn't available in sys.parameters.

As of SQL Server Management Studio 18, the logic for this is in Microsoft.SqlServer.SqlEnum.dll, specifically, the class Microsoft.SqlServer.Management.Smo.PostProcessParam. You'll find other useful regexes in there too.

Here's the patterns:

If your procedure was saved with SET QUOTED_IDENTIFIER then this pattern is used:

new Regex( "(/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)|(--[^\n]*)|(\"((\"\")|[^\"])*\")|(//[^\n]*)|(?<delim>\\b((AS)|(RETURNS))\\b)|(?:(?<param>@[\\w_][\\w\\d_$$@#]*)((\\s)|((--[^\n]*))|((/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)))*(AS){0,1})|(?<val>(((\"((\"\")|[^\"])*\"))|((N{0,1}'(('')|[^'])*)')|((0x[0-9a-f]+))|(((\\+|\\-){0,1}((\\d+\\.\\d*)|(\\d*\\.\\d+)|(\\d+))(e((\\+)|(\\-))\\d+){0,1}))|((\\[((\\]\\])|[^\\]])*\\]))|(([\\w_][\\w;\\d_]*))))|(?<comma>,)|(?<eq>=)|(\\([\\d, ]*\\))", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);

If your procedure was not saved with SET QUOTED_IDENTIFIER then this pattern is used:

new Regex( "(/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)|(--[^\n]*)|(//[^\n]*)|(?<delim>\\b((AS)|(RETURNS))\\b)|(?:(?<param>@[\\w_][\\w\\d_$$@#]*)((\\s)|((--[^\n]*))|((/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)))*(AS){0,1})|(?<val>(((\"((\"\")|[^\"])*\"))|((N{0,1}'(('')|[^'])*)')|((0x[0-9a-f]+))|(((\\+|\\-){0,1}((\\d+\\.\\d*)|(\\d*\\.\\d+)|(\\d+))(e((\\+)|(\\-))\\d+){0,1}))|((\\[((\\]\\])|[^\\]])*\\]))|(([\\w_][\\w;\\d_]*))))|(?<comma>,)|(?<eq>=)|(\\([\\d, ]*\\))", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);

To get the default values of each parameter, run the appropriate regex against your sql.sql_modules_definition and watch for the val group.


TL;DR:

Here's the full instructions needed to get this to work:

  • Requirements:
    • On-Prem SQL Server (tested with SQL Server 2014, 2016 and 207). This does not work with Azure SQL because Azure SQL does not support SQL-CLR.
    • You do not need Visual Studio - just a command-line access to csc.exe so you can compile the SQL-CLR assembly yourself.
  1. Copy and paste the GetParameterDefaults.cs file described below.
  2. Run this command in a terminal/command-prompt to compile GetParameterDefaults.cs to a SQL-CLR assembly ProcParamDefs.dll:
    csc.exe /noconfig /nowarn:1701,1702,2008 /fullpaths /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\mscorlib.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.Data.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.Xml.dll" /debug+ /debug:full /optimize- /out:ProcParamDefs.dll /subsystemversion:6.00 /target:library /warnaserror- /utf8output /langversion:7.3 GetParameterDefaults.cs 
    
    
  3. Locate the ProcParamDefs.dll you created in Step 2 and convert it to a hex-bin string (Base 16).
    • There are websites that will do it for free, or use this PowerShell command:
      ( Get-Content ".\ProcParamDefs.dll" | Format-Hex | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join ''
      
  4. Copy and paste the Install.sql file (at the end of this post) into an SSMS session.
  5. Replace <PASTE BASE-16 (HEX) DLL HERE KEEP THE LEADING 0x PREFIX> with the Base16/hex from step 3. Note the leading 0x needs to be present, as does the trailing ;, so it should look something like this:
CREATE ASSEMBLY [ProcParamDefs]
  AUTHORIZATION [dbo]
  FROM 0x0x4D5A90000300000004000000FFFF0000B800etc
;
  1. Run it and you should be all-set to use the UDF dbo.GetParameterDefaults, dbo.[GetParameterDefaultsByProcedureObjectId, and dbo.ParseParameterDefaultValues.

  2. For example, see this screenshot:

Is there a solution for getting the default value of the parameters of a given stored procedure?

Note that it only lists parameters with defaults. It will not list parameters without any default defined.

GetParameterDefaults.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

/// <summary>
/// This SQL-CLR table-valued UDF will parse and expose the default-values for SQL Server stored procedures as this information is not normally available through INFORMATION_SCHEMA nor sys.parameters.<br />
/// By Dai Rees on StackOverflow: https://stackoverflow.com/questions/6992561/is-there-a-solution-for-getting-the-default-value-of-the-parameters-of-a-given-s<br />
/// This also may find its way onto my GitHub eventually too.<br />
/// The crucial regular-expressions inside this UDF were copied from Microsoft's SqlEnum.dll (they're *the exact same* regexes that SSMS uses to show parameter information) btw.<br />
/// I guess the regexes are Microsoft's copyright, but SSMS is given-away for free and Microsoft made no attempt to obscure/hide them, so I guess this is fair-use? If so, then consider my code as MIT licensed, so happy forking!
/// </summary>
public partial class UserDefinedFunctions
{
    private static readonly Regex _paramRegexQI = new Regex("(/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)|(--[^\n]*)|(\"((\"\")|[^\"])*\")|(//[^\n]*)|(?<delim>\\b((AS)|(RETURNS))\\b)|(?:(?<param>@[\\w_][\\w\\d_$$@#]*)((\\s)|((--[^\n]*))|((/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)))*(AS){0,1})|(?<val>(((\"((\"\")|[^\"])*\"))|((N{0,1}'(('')|[^'])*)')|((0x[0-9a-f]+))|(((\\+|\\-){0,1}((\\d+\\.\\d*)|(\\d*\\.\\d+)|(\\d+))(e((\\+)|(\\-))\\d+){0,1}))|((\\[((\\]\\])|[^\\]])*\\]))|(([\\w_][\\w;\\d_]*))))|(?<comma>,)|(?<eq>=)|(\\([\\d, ]*\\))", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture | RegexOptions.Compiled );
    private static readonly Regex _paramRegex   = new Regex("(/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)|(--[^\n]*)|(//[^\n]*)|(?<delim>\\b((AS)|(RETURNS))\\b)|(?:(?<param>@[\\w_][\\w\\d_$$@#]*)((\\s)|((--[^\n]*))|((/\\*(([^/\\*])|(\\*(?=[^/]))|(/(?=[^\\*])))*|(/\\*(?>/\\*(?<DEPTH>)|\\*/(?<-DEPTH>)|(.|[\n])?)*(?(DEPTH)(?!))\\*/)\\*/)))*(AS){0,1})|(?<val>(((\"((\"\")|[^\"])*\"))|((N{0,1}'(('')|[^'])*)')|((0x[0-9a-f]+))|(((\\+|\\-){0,1}((\\d+\\.\\d*)|(\\d*\\.\\d+)|(\\d+))(e((\\+)|(\\-))\\d+){0,1}))|((\\[((\\]\\])|[^\\]])*\\]))|(([\\w_][\\w;\\d_]*))))|(?<comma>,)|(?<eq>=)|(\\([\\d, ]*\\))", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture | RegexOptions.Compiled );
    
    private const String _tableDefinition = @"ParameterName nvarchar(100), DefaultValueExpr nvarchar(4000)";

    [SqlFunction(
        DataAccess        = DataAccessKind.Read,
        FillRowMethodName = nameof(FillRow),
        IsDeterministic   = false,
        Name              = nameof(GetParameterDefaults),
        SystemDataAccess  = SystemDataAccessKind.Read,
        TableDefinition   = _tableDefinition
    )]
    public static IEnumerable/*<(String parameterName, String defaultValueExpr)>*/ GetParameterDefaults( String procedureName )
    {
        // Despite the fact the function returns an IEnumerable and the SQLCLR docs saying how great streaming is, it's actually very difficult to use `yield return`:
        // See here: https://stackoverflow.com/questions/591191/sqlfunction-fails-to-open-context-connection-despite-dataaccesskind-read-present

        // SQLCLR will handle ArgumentExceptions just fine: 
        // https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/server-side-error-handling-part-2-errors-and-error-messages
        // https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/exception-handling-in-sqlclr
        if( procedureName is null ) throw new ArgumentNullException( paramName: nameof(procedureName) );
        if( String.IsNullOrWhiteSpace( procedureName ) ) throw new ArgumentException( message: "Value cannot be empty nor whitespace.", paramName: nameof(procedureName) );
        
        //

        ( Boolean ok, String definition, Boolean isQuotedId ) = TryGetProcedureDefinitionFromName( procedureName );
        if( !ok )
        {
            throw new ArgumentException( message: "Could not find the definition of a procedure with the name \"" + procedureName + "\".", paramName: nameof(procedureName) );
        }

        // We can't do this, boo:
        // foreach( var t in ParseParams( definition, quotedId ? _paramRegexQI : _paramRegex ) ) yield return t;

        return ParseParameterDefaultValues( definition, isQuotedId );
    }

    [SqlFunction(
        DataAccess        = DataAccessKind.Read,
        FillRowMethodName = nameof(FillRow),
        IsDeterministic   = false,
        Name              = nameof(GetParameterDefaultsByProcedureObjectId),
        SystemDataAccess  = SystemDataAccessKind.Read,
        TableDefinition   = _tableDefinition
    )]
    public static IEnumerable/*<(String parameterName, String defaultValueExpr)>*/ GetParameterDefaultsByProcedureObjectId( Int32 procedureObjectId )
    {
        ( Boolean ok, String definition, Boolean isQuotedId ) = TryGetProcedureDefinitionFromId( procedureObjectId );
        if( !ok )
        {
            throw new ArgumentException( message: "Could not find the definition of a procedure with OBJECT_ID = " + procedureObjectId.ToString(), paramName: nameof(procedureObjectId) );
        }

        // We can't do this, boo:
        // foreach( var t in ParseParams( definition, quotedId ? _paramRegexQI : _paramRegex ) ) yield return t;

        return ParseParameterDefaultValues( definition, isQuotedId );
    }

    [SqlFunction(
        DataAccess        = DataAccessKind.Read,
        FillRowMethodName = nameof(FillRow),
        IsDeterministic   = false,
        Name              = nameof(ParseParameterDefaultValues),
        SystemDataAccess  = SystemDataAccessKind.Read,
        TableDefinition   = _tableDefinition
    )]
    public static IEnumerable/*<(String parameterName, String defaultValueExpr)>*/ ParseParameterDefaultValues( String procedureDefinition, Boolean isQuotedId )
    {
        List<(String parameterName, String defaultValueExpr)> list = new List<(String parameterName, String defaultValueExpr)>();

        foreach( (String name, String value) t in ParseParams( procedureDefinition, isQuotedId ? _paramRegexQI : _paramRegex ) )
        {
            list.Add( t );
        }
        
        return list;
    }

    private static ( Boolean ok, String definition, Boolean quotedId ) TryGetProcedureDefinitionFromName( String procedureName )
    {
        using( SqlConnection c = new SqlConnection( "context connection=true" ) )
        {
            c.Open();

            using( SqlCommand cmd = c.CreateCommand() )
            {
                cmd.CommandText = @"
SELECT
    c.[definition],
    CONVERT( bit, OBJECTPROPERTY( c.object_id, N'ExecIsQuotedIdentOn' ) ) AS IsQuotedId
FROM
    sys.sql_modules AS c
WHERE
    c.object_id = OBJECT_ID( @procedureName );";

                _ = cmd.Parameters.Add( new SqlParameter( "@procedureName", SqlDbType.NVarChar ) { Value = procedureName } );

                using( SqlDataReader rdr = cmd.ExecuteReader() )
                {
                    if( rdr.Read() )
                    {
                        String definition = rdr.GetString(0);
                        Boolean quotedId = rdr.GetBoolean(1);
                        return ( ok: true, definition, quotedId );
                    }
                    else
                    {
                        // Validate the object-name:

                        return ( ok: false, definition: null, quotedId: default );
                    }
                }
            }

            /*
            using( SqlCommand cmdPostMortem = c.CreateCommand() )
            {
                cmdPostMortem.CommandText = @"
SELECT OBJECT_ID( @procedureName ) AS oid";

                _ = cmdPostMortem.Parameters.Add( new SqlParameter( "@procedureName", SqlDbType.NVarChar ) { Value = procedureName } );

                Object objectId = cmdPostMortem.ExecuteScalar();
                if( objectId is null || objectId == DBNull.Value )
                {

                }
            }
            */
        }
    }

    private static ( Boolean ok, String definition, Boolean quotedId ) TryGetProcedureDefinitionFromId( Int32 procedureObjectId )
    {
        using( SqlConnection c = new SqlConnection( "context connection=true" ) )
        {
            c.Open();

            using( SqlCommand cmd = c.CreateCommand() )
            {
                cmd.CommandText = @"
SELECT
    c.[definition],
    CONVERT( bit, OBJECTPROPERTY( c.object_id, N'ExecIsQuotedIdentOn' ) ) AS IsQuotedId
FROM
    sys.sql_modules AS c
WHERE
    c.object_id = @objId;";

                _ = cmd.Parameters.Add( new SqlParameter( "@objId", SqlDbType.Int ) { Value = procedureObjectId } ); // `OBJECT_ID` returns `int` values btw.

                using( SqlDataReader rdr = cmd.ExecuteReader() )
                {
                    if( rdr.Read() )
                    {
                        String definition = rdr.GetString(0);
                        Boolean quotedId = rdr.GetBoolean(1);
                        return ( ok: true, definition, quotedId );
                    }
                    else
                    {
                        return ( ok: false, definition: null, quotedId: default );
                    }
                }
            }
        }
    }

    private static IEnumerable<(String name, String value)> ParseParams( String definition, Regex r )
    {
        Boolean inParam = false;
        String currentParameterName = null;

        Match m = r.Match( definition );

        while( m.Success && !m.Groups["delim"].Success )
        {
            if( m.Groups["eq"].Success )
            {
                inParam = true;
            }

            if( m.Groups["comma"].Success )
            {
                inParam = false;
                currentParameterName = null;
            }

            if( inParam && currentParameterName != null && m.Groups["val"].Success )
            {
                String defaultValue = m.Groups["val"].Value;
                inParam = false;

                yield return ( currentParameterName, defaultValue );
            } 

            if( m.Groups["param"].Success )
            {
                currentParameterName = m.Groups["param"].Value;
            }

            m = m.NextMatch();
        }
    }

    public static void FillRow( Object tupleObj, out SqlString parameterName, out SqlString defaultValueExpr )
    {
        if( tupleObj is ValueTuple<String,String> vt )
        {
            parameterName    = vt.Item1;
            defaultValueExpr = vt.Item2;
        }
        else if( tupleObj is null )
        {
            throw new ArgumentNullException( paramName: nameof(tupleObj) );
        }
        else
        {
            throw new ArgumentException( message: "Expected first argument to be of type ValueTuple<String,String> but encountered " + tupleObj.GetType().FullName, paramName: nameof(tupleObj) );
        }
    }
}

Install.sql

/* You may need to run these statements too:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

GO

*/

CREATE ASSEMBLY [ProcParamDefs] AUTHORIZATION [dbo]
FROM 0x<PASTE BASE-16 (HEX) DLL HERE KEEP THE LEADING 0x PREFIX>
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[GetParameterDefaults] (@procedureName [nvarchar](MAX))
RETURNS TABLE (ParameterName nvarchar(100), DefaultValueExpr nvarchar(4000))
AS EXTERNAL NAME [ProcParamDefs].[UserDefinedFunctions].[GetParameterDefaults];

GO

CREATE FUNCTION [dbo].[GetParameterDefaultsByProcedureObjectId] (@procedureObjectId [int])
RETURNS TABLE (ParameterName nvarchar(100), DefaultValueExpr nvarchar(4000))
AS EXTERNAL NAME [ProcParamDefs].[UserDefinedFunctions].[GetParameterDefaultsByProcedureObjectId];

GO

CREATE FUNCTION [dbo].[ParseParameterDefaultValues] (@procedureDefinition [nvarchar](MAX), @isQuotedId [bit])
RETURNS TABLE (ParameterName nvarchar(100), DefaultValueExpr nvarchar(4000))
AS EXTERNAL NAME [ProcParamDefs].[UserDefinedFunctions].[ParseParameterDefaultValues];

GO


As said before, this is not supported via T-SQL.

You'll have to use some kind of program language to implement this (unless you want to deal with text parsing with T-SQL).

I found it very easy to use SMO for this.

For example, in Powershell:

param
(
    [string]$InstanceName = $env:COMPUTERNAME,
    [string]$DBName = "MyDB",
    [string]$ProcedureSchema = "dbo",
    [string]$ProcedureName = "MyProcedure"
)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

$serverInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.StoredProcedure], $false)

$procedure = $serverInstance.Databases[$DBName].StoredProcedures[$ProcedureName, $ProcedureSchema];

$procedure.Parameters | Select-Object Parent, Name, DataType, DefaultValue, @{Name="Properties";Expression={$_.Properties | Select Name, Value }}

Or, using C#:

        Server svr = new Server(new ServerConnection(new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)));
        svr.SetDefaultInitFields(typeof(StoredProcedure), false);
        StoredProcedure sp = svr.Databases["MyDatabase"].StoredProcedures["mySproc", "myScheme"];

        Dictionary<string, string> defaultValueLookup = new Dictionary<string, string>();
        foreach (StoredProcedureParameter parameter in sp.Parameters)
        {
            string defaultValue = parameter.DefaultValue;
            string parameterName = parameter.Name;
            defaultValueLookup.Add(parameterName, defaultValue);
        }

(source for the last one: https://stackoverflow.com/a/9977237/3114728)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜