Home Dashboard Directory Help

Populate has_default_value in sys.parameters by AaronBertrand


Status: 

Active


59
0
Sign in
to vote
Type: Suggestion
ID: 234143
Opened: 10/31/2006 10:15:30 PM
Access Restriction: Public
Duplicates: 362784
5
Workaround(s)
view

Description

As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, all_parameters, and system_parameters. They are also not exposed through sp_sproc_columns, sys.columns, or sp_procedure_params_rowset.

However, Management Studio has no problems at least indicating whether or not a default value exists (though not the actual value) for a specific parameter (this is new in 2005; Query Analyzer did not show this). So I know that the information can be retrieved somehow, whether it is parsing the text in sys.sql_modules.definition, or using spt_sproc_columns_odbc_view, or some internal mechanism invisible to profiler.

This is not an overly common request, but it happens enough in the community to spur me to submit this feedback. For the most recent thread about it, see:

http://tinyurl.com/yaw3fj
Details
Sign in to post a comment.
Posted by AgeofMachines on 2/22/2010 at 12:31 AM
Update: I just finished it tonight and put it to use. NO CURSORS!!!
Posted by AgeofMachines on 2/21/2010 at 9:05 PM
And whatever solution you come up with SHOULD NOT USE cursors. Can it be done? Yes. I could do it without cursors - for a fee.......
Posted by JGWeissman on 11/6/2009 at 11:28 AM
Andrew from Microsoft,

Your response on this issue is simply not acceptable. You need to be aware that this metadata is accessed and used programatically, and you should never expect your customers to parse scripts to get values that should be exposed in structured metadata. Saying you will look into addressing the issue in future releases, and then doing nothing for three years is not the right answer. This should be a simple problem to fix, and you should release update for it quickly.
Posted by AaronBertrand on 11/13/2006 at 2:56 PM
Hi Andrew, this is Aaron not Tim. If you are not gong to fix these omissions from sys.parameters, then could you at least share/publish the code that Management Studio uses to parse syscomments.text, since it is fully capable of showing Default or No Default in Object Explorer (see attachment). I think this would be useful in order to prevent people who want this functionality from re-inventing the wheel.
Posted by Microsoft on 11/13/2006 at 2:23 PM
HI Tim,

As posted by Tibor Karaszi, BOL document that "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."

We dont store even the bit that indicating parameter is of default value in Yukon.

Thanks for the feedback, we will look into addressing this more completely in future releases.

Best Regards
Andrew
[MSFT]


Sign in to post a workaround.
Posted by Tom Groszko on 4/24/2013 at 7:16 AM


IF (OBJECT_ID('dbo.CleanSQLTable', 'TF') IS NULL)
BEGIN EXEC('CREATE FUNCTION dbo.CleanSQLTable() RETURNS @SomeTable TABLE (SomeColumn INT) WITH SCHEMABINDING AS BEGIN INSERT @SomeTable (SomeColumn) SELECT 123; RETURN; END;' )
END;
GO
ALTER FUNCTION dbo.CleanSQLTable
(    @ObjectText        NVARCHAR(MAX)    
)
RETURNS    @ObjectTextTable        TABLE
(     ObjectText                    NVARCHAR(MAX)    
    ,LineCommentSuccess            BIT
    ,MultiLineCommentSuccess    BIT
)
AS
BEGIN;    
-- Remove comments and some white space from T-SQL
DECLARE @SanityCheck                INT    =    0
        ,@Insane                    INT    =    5000
        ,@LineCommentSuccess        BIT    =    1
        ,@MultiLineCommentSuccess    BIT =    1;
-- When comment delimiters are embedded within comments this will not work.
SELECT @SanityCheck    =    0;
WHILE PATINDEX(N'%--%',@ObjectText) <> 0
BEGIN;    SELECT    @ObjectText        =
                SUBSTRING    (     @ObjectText
                                ,1
                                ,PATINDEX(N'%--%',@ObjectText) - 1
                            )
            +    SUBSTRING    (     @ObjectText
                                ,Isnull(CHARINDEX(CHAR(13),@ObjectText, PATINDEX(N'%--%',@ObjectText)),LEN(@ObjectText))
                                ,LEN(@ObjectText)
                            );
        SELECT @SanityCheck += 1;
        if    (@SanityCheck > @Insane)
        BEGIN;    SELECT     @LineCommentSuccess    = 0;
                BREAK;
        END;
END;
SELECT @SanityCheck    =    0;
WHILE (CHARINDEX(N'/*',@ObjectText) <> 0)
BEGIN;    IF    (    (CHARINDEX(N'*/',@ObjectText) + 2)
            <    (CHARINDEX(N'/*',@ObjectText) - 1)
            )
        BEGIN;    SELECT @ObjectText = NULL;
                BREAK;
        END;
        SELECT    @ObjectText        =
                SUBSTRING    (     @ObjectText
                                ,1
                                ,CHARINDEX(N'/*',@ObjectText) - 1
                            )
            +    SUBSTRING    (     @ObjectText
                                ,CHARINDEX(N'*/',@ObjectText) + 2
                                ,LEN(@ObjectText)
                            );
        SELECT @SanityCheck += 1;
        if    (@SanityCheck > @Insane)
        BEGIN;    SELECT @MultiLineCommentSuccess    =    0;
                BREAK;
        END;
END;
SELECT    @ObjectText = REPLACE(REPLACE(REPLACE(@ObjectText, CHAR(10), N''), CHAR(9), N' '), CHAR(13), N' ');
DECLARE     @StartLength    BIGINT    =    1
        ,@NewLength        BIGINT    =    0;
WHILE    (@StartLength != @NewLength)
BEGIN;    SELECT @StartLength    =    LEN(@ObjectText);
        SELECT @ObjectText    =    REPLACE(@ObjectText, N' ', N' ');
        SELECT @NewLength    =    LEN(@ObjectText);
END;
INSERT INTO    @ObjectTextTable
(     ObjectText                    
    ,LineCommentSuccess            
    ,MultiLineCommentSuccess    
)
SELECT     @ObjectText
        ,@LineCommentSuccess
        ,@MultiLineCommentSuccess;
RETURN;
END;
GO
--    ========================================
--    ========================================
--    ========================================
IF (OBJECT_ID('dbo.ParameterDefault', 'IF') IS NULL)
BEGIN EXEC('CREATE FUNCTION dbo.ParameterDefault() RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT ''This should be replaced'' OOPS);' )
END;
GO
ALTER FUNCTION dbo.ParameterDefault
(    @ObjectID INT
)
RETURNS TABLE
AS
RETURN
(    
--    Pick out the default for parameters for procedure and function parameters.
--    original code found at http://stackoverflow.com/questions/7773053/find-which-parameters-of-stored-procedures-are-nullable-optional
--    re written by MTGSystems@charter.net
WITH    SQLModuleData AS    
(    SELECT     sql_modules.object_id                            ObjectID
            ,schemas.name                                    SchemaName
            ,objects.name                                    ObjectName
            --    There is no certainty that the ' AS ' will be bounded by a space
            --    when it is not this will not work, the alternative is to
            --    handle the letters 'as' within a literal that is part of a default
            --    just a swag as to which way to handle this.
            --    CleanSQL removes comments and some white space from the code. Slow but relieves this code from
            --    having to account for comments.
            ,CASE    WHEN    (ABS(PATINDEX(N'%[ )]AS[ ]%', CleanSQL.ObjectText))
                                -    CHARINDEX(objects.name, CleanSQL.ObjectText) - LEN(objects.name))
                        > 0
                    THEN    SUBSTRING    (     CleanSQL.ObjectText
                                            ,CHARINDEX(objects.name, CleanSQL.ObjectText    ) + LEN(objects.name) + 1
                                            ,ABS(PATINDEX(N'%[ )]AS[ ]%', CleanSQL.ObjectText))
                                                -    CHARINDEX(objects.name, CleanSQL.ObjectText    ) - LEN(objects.name)
                                        )                                    
                    ELSE NULL
             END                                            ModuleHeader
    FROM    sys.all_sql_modules    sql_modules    
    CROSS    APPLY dbo.CleanSQLTable(sql_modules.definition) CleanSQL
    JOIN    sys.all_objects        objects        ON    sql_modules.object_id    = objects.object_id
    JOIN    sys.schemas            schemas        ON    objects.schema_id        = schemas.schema_id
    WHERE    sql_modules.object_id = @ObjectID
    AND sql_modules.definition IS NOT NULL    -- Encrypted stuff is null
)
, ParameterNames AS
(    SELECT     SQLModuleData.ObjectID                                        ObjectID    
            ,SQLModuleData.SchemaName                                    SchemaName
            ,SQLModuleData.ObjectName                                    ObjectName
            ,parameters1.name                                            ParameterName
            ,parameters1.parameter_id                                    ParameterID
            ,SQLModuleData.ModuleHeader                                    ModuleHeader
            ,LEN(parameters1.name)                                        ParameterNameLength    
            ,PATINDEX('%' + parameters1.name + ' %', SQLModuleData.ModuleHeader)    TokenPosition
            ,PATINDEX('%' + parameters2.name + ' %', SQLModuleData.ModuleHeader)    NextTokenPosition
    FROM        SQLModuleData
    JOIN        sys.all_parameters parameters1 ON    SQLModuleData.ObjectID            = parameters1.object_id
    LEFT JOIN    sys.all_parameters parameters2 ON    SQLModuleData.ObjectID            = parameters2.object_id
                                                AND parameters1.parameter_id + 1    = parameters2.parameter_id
    WHERE SQLModuleData.ModuleHeader LIKE N'%=%' -- Only interesting when there is at lease one parameter.
)
,    DataTypeWithDefault AS
(    SELECT     ParameterNames.ObjectID                ObjectID    
            ,ParameterNames.SchemaName                SchemaName
            ,ParameterNames.ObjectName                ObjectName
            ,ParameterNames.ParameterName            ParameterName
            ,ParameterNames.ParameterID                ParameterID
            ,SUBSTRING    (     ParameterNames.ModuleHeader
                            ,ParameterNames.TokenPosition + ParameterNames.ParameterNameLength + 1
                            ,ISNULL(ABS(ParameterNames.NextTokenPosition - ParameterNames.TokenPosition - ParameterNames.ParameterNameLength - 1), LEN(ParameterNames.ModuleHeader))
                        )                            DataTypeWithDefault
    FROM    ParameterNames
)
,    DirtyDefaultValue AS
(    SELECT     DataTypeWithDefault.ObjectID            ObjectID    
            ,DataTypeWithDefault.SchemaName            SchemaName
            ,DataTypeWithDefault.ObjectName            ObjectName
            ,DataTypeWithDefault.ParameterName        ParameterName
            ,DataTypeWithDefault.ParameterID        ParameterID
            ,RTRIM(LTRIM(    REPLACE    (     SUBSTRING    (     DataTypeWithDefault.DataTypeWithDefault
                                                        ,CHARINDEX(N'=', DataTypeWithDefault.DataTypeWithDefault, 1) + 1
                                                        ,1000 -- Should be large enough
                                                    )
                                        ,N'OUTPUT'    --    the fact that a parameter is output is not relevant
                                                    --    if the string appears in a literal it will also be removed
                                                    --    perhaps a bug that I am not going to deal with for now.
                                        ,N'')
                        ))                            DirtyDefaultValue
    FROM    DataTypeWithDefault
    WHERE    DataTypeWithDefault.DataTypeWithDefault LIKE N'%=%' -- Only want those with a default.
)
,    CleanDefaultValue AS
(    SELECT     DirtyDefaultValue.ObjectID                ObjectID    
            ,DirtyDefaultValue.SchemaName            SchemaName
            ,DirtyDefaultValue.ObjectName            ObjectName
            ,DirtyDefaultValue.ParameterName        ParameterName
            ,DirtyDefaultValue.ParameterID            ParameterID
            ,CASE    --    get rid of trailing ,
                    WHEN    RIGHT(DirtyDefaultValue.DirtyDefaultValue, 1) = N','
                    THEN    LEFT(DirtyDefaultValue.DirtyDefaultValue, LEN(DirtyDefaultValue.DirtyDefaultValue) - 1)
                    --    when the last parameter has a default value.
                    WHEN    DirtyDefaultValue.DirtyDefaultValue LIKE N'%)%'
                    THEN    RTRIM(LEFT(DirtyDefaultValue.DirtyDefaultValue, (CHARINDEX(N')', DirtyDefaultValue.DirtyDefaultValue)- 1)))
                    --    bet there are more items as yet not found.
                    ELSE    DirtyDefaultValue.DirtyDefaultValue
             END                                    DefaultValue
            --,DirtyDefaultValue.DirtyDefaultValue DefaultValue
    FROM    DirtyDefaultValue
)
SELECT     CleanDefaultValue.ObjectID
        ,CleanDefaultValue.SchemaName
        ,CleanDefaultValue.ObjectName
        ,CleanDefaultValue.ParameterName
        ,CleanDefaultValue.ParameterID
        ,CleanDefaultValue.DefaultValue
FROM CleanDefaultValue
);
GO
--    ========================================
--    ========================================
--    ========================================

SELECT     objects.name
        ,parameters.name
        ,ParameterDefault.DefaultValue
FROM sys.objects
LEFT JOIN    sys.parameters            parameters            
    ON        objects.object_id    =    parameters.object_id
CROSS APPLY    dbo.ParameterDefault(parameters.object_id)    ParameterDefault
WHERE objects.type    in
            (     'FN'    --    SQL_SCALAR_FUNCTION
                ,'IF'    --    SQL_INLINE_TABLE_VALUED_FUNCTION
                ,'TF'    --    SQL_TABLE_VALUED_FUNCTION
                ,'P '    --    SQL_STORED_PROCEDURE
            )
Posted by Tom Groszko on 4/18/2013 at 11:41 AM
Certainly not mine but this also seems to work

https://connect.microsoft.com/SQLServer/feedback/details/234143/populate-has-default-value-in-sys-parameters

go to the last comment.
Posted by pkral on 5/15/2012 at 11:16 AM
# PowerShell script using SMO to create list of stored procedure parameters
# Workaround for T-SQL stored procedure parameter defaults not available in sys.parameters

# Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;

# Connect to SQL Server
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "MyServer";
$db = $srv.Databases["MyDatabase"];

# Loop through the Stored Procedures
foreach ($proc in $db.StoredProcedures){
# Loop through the Stored Procedure Parameters
foreach($parameter in $proc.Parameters) {
if ($parameter.DefaultValue){
     Write-Host "$proc , $parameter , $($parameter.DefaultValue)";
}
else{
     Write-Host "$proc , $parameter , No Default Value";
};
};
};
Posted by AaronBertrand on 11/1/2006 at 10:54 AM
Note that the workaround assumes that the procedure does not have comments preceding or interspersed with the input parameter list.
Posted by AaronBertrand on 11/1/2006 at 10:46 AM
/*
This is kind of brute force, but parses object_definition for parameters that have default values. Working on a version that also retrieves the default value as NVARCHAR but parsing there is proving a little trickier.
*/

CREATE PROCEDURE dbo.sys_GetParameters
    @object_name NVARCHAR(511)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @object_id INT,
        @paramID INT,
        @paramName SYSNAME,
        @definition NVARCHAR(MAX),
        @t NVARCHAR(MAX),
        @loc1 INT,
        @loc2 INT,
        @loc3 INT,
        @loc4 INT,
        @has_default_value BIT;

    SET @object_id = OBJECT_ID(@object_name);

    IF (@object_id IS NOT NULL)
    BEGIN
    
        SELECT @definition = OBJECT_DEFINITION(@object_id);

        CREATE TABLE #params
        (
            parameter_id        INT PRIMARY KEY,
            has_default_value    BIT NOT NULL DEFAULT (0)
        );

        DECLARE c CURSOR
        LOCAL FORWARD_ONLY STATIC READ_ONLY
        FOR
            SELECT
                parameter_id,
                [name]
            FROM
                sys.parameters
            WHERE
                [object_id] = @object_id;

        OPEN c;

        FETCH NEXT FROM c INTO @paramID, @paramName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
    
            SELECT
                @t = SUBSTRING
                (
                    @definition,
                    CHARINDEX(@paramName, @definition),
                    4000
                ),
                @has_default_value = 0;
            
            SET @loc1 = COALESCE(NULLIF(CHARINDEX('''', @t), 0), 4000);
            SET @loc2 = COALESCE(NULLIF(CHARINDEX(',', @t), 0), 4000);
            SET @loc3 = NULLIF(CHARINDEX('OUTPUT', @t), 0);
            SET @loc4 = NULLIF(CHARINDEX('AS', @t), 0);
            
            SET @loc1 = CASE WHEN @loc2 < @loc1 THEN @loc2 ELSE @loc1 END;
            SET @loc1 = CASE WHEN @loc3 < @loc1 THEN @loc3 ELSE @loc1 END;
            SET @loc1 = CASE WHEN @loc4 < @loc1 THEN @loc4 ELSE @loc1 END;
            
            IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@t, 1, @loc1)))) > 0
                SET @has_default_value = 1;

            INSERT #params
            (
                parameter_id,
                has_default_value
            )
            SELECT
                @paramID,
                @has_default_value;
            
            FETCH NEXT FROM c INTO @paramID, @paramName;
        END
        
        SELECT
            sp.[object_id],
            [object_name] = @object_name,
            param_name = sp.[name],
            sp.parameter_id,
            type_name = UPPER(st.[name]),
            sp.max_length,
            sp.[precision],
            sp.scale,
            sp.is_output,
            p.has_default_value
        FROM
            sys.parameters sp
        INNER JOIN
            #params p
        ON
            sp.parameter_id = p.parameter_id
        INNER JOIN
            sys.types st
        ON
            sp.user_type_id = st.user_type_id
        WHERE
            sp.[object_id] = @object_id;
        
        CLOSE c;
        DEALLOCATE c;
        DROP TABLE #params;
    END
END
GO
File Name Submitted By Submitted On File Size  
default_or_no_default.zip (restricted) 11/13/2006 -