Determine whether SP Parameter has a Default Value in T-SQL

Question

Is there any way to determine from within SQL Server (I'm on 2012 FYI) if a SP's parameters have default values? There are other threads on this, however the suggestions don't seem to get me this information accurately.

Here are a couple of things I've tried;

select *
from sys.objects so join sys.parameters sp on so.object_id = sp.object_id
where so.type='P'
and so.name = 'someSp'

The above query returns a number of columns that sound like I'm barking up the right tree (has_default_value, default_value among them) but these don't seem to vary whether I have a default value in my SP or not. (has_default value is always 0, default_value is always null)

exec sp_sproc_columns 'someSp'

Same deal; the above SP returns a number of columns including NULLABLE and IS_NULLABLE; NULLABLE is always equal to 1 and IS_NULLABLE = YES, regardless of my SP contents.

A note; SQL Server management studio clearly displays the metadata associated with a each SP Parameter.

I've used SQL Profiler to examine what happens when I view the parameters of a SP in Management Studio's Object Explorer. When you expand the parameters folder, there are two queries run. The first query is a bit long for pasting here (though I'll do so if helpful). It contains a column called DEFAULT VALUE; however it's always NULL as far as I can tell. The second query simply returns the body of the SP; presumably to output to the text editor window (though I'm afraid there could be some parsing happening within mgmt studio!)

For reference / just to make sure I'm not losing my marbles I've created two meaningless Sps just for testing. They look like:

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

Answer 1

if parameter name have "AS" symbols - do not work try my

create procedure ViewParameters
    @procedure varchar(50)
as
    declare
        @w varchar(max),
        @p int, @p2 int,
        @t varchar(max)


    /* Andrey Rubanko 18 jul 2013 */

    /* fill temporary table with procedure body */

    select @w = definition
    from sys.sql_modules
    where object_id = object_id(@procedure)

    declare @lines table (line varchar(500), id int identity(1, 1))

    while len(@w) > 0 begin
        set @p = charindex(char(10), @w)
        if @p > 0 begin
            insert @lines(line) values(replace(replace(SUBSTRING(@w, 1, @p - 1), char(13), ''), char(9), ' '))
            set @w = SUBSTRING(@w, @p + 1, 10000)
        end else begin
            insert @lines(line) values(replace(@w, char(13), ''))
            set @w = ''
        end
    end



    /* remove comments */

    declare 
        @i int,
        @inCommentNow bit,
        @again bit

    set @i = 1
    set @inCommentNow = 0

    while @i <= (select max(id) from @lines) begin
        select @w = line from @lines where id = @i
        set @again = 0

        if @inCommentNow = 0 begin
            set @p = patindex('%--%', @w)
            if @p > 0 begin
                set @w = SUBSTRING(@w, 1, @p - 1)

                update @lines
                set line = @w
                where id = @i

            end

            set @p = patIndex('%/*%', @w)
            if @p > 0 begin
                set @p2 = PATINDEX('%*/%', @w) 
                if @p2 > 0 begin
                    update @lines
                    set line = substring(@w, 1, @p - 1) + SUBSTRING(@w, @p2 + 2, 10000)
                    where id = @i

                    set @again = 1
                end else begin
                    set @inCommentNow = 1

                    update @lines
                    set line = SUBSTRING(@w, 1, @p - 1)
                    where id = @i
                end
            end
        end

        if @inCommentNow = 1 begin
            set @p = PATINDEX('%*/%', @w)
            if @p > 0 begin
                update @lines
                set line = SUBSTRING(@w, @p + 2, 10000)
                where id = @i

                set @inCommentNow = 0
                set @again = 1
            end else 
                update @lines
                set line = ''
                where id = @i
        end

        if @again = 0
            set @i = @i + 1
    end


    /* remove all except parameters */
    declare
        @first int,
        @last int

    set @i = 1

    while @last is null begin
        select @w = line from @lines where id = @i

        if SUBSTRING(@w, 1, 2) = 'as'
            set @last = @i - 1

        set @p = PATINDEX('% as%', @w) 
        if @last is null and @p > 0  begin
            set @w = SUBSTRING(@w, 1, @p - 1)

            update @lines
            set line = @w
            where id = @i

            if charindex('@', @w) > 0
                set @last = @i
            else 
                set @last = @i - 1
        end


        set @p = CHARINDEX('@', @w)
        if @first is null and @p > 0 begin
            set @first = @i
            set @w = SUBSTRING(@w, @p, 10000)
        end

        set @i = @i + 1
    end

    delete @lines
    where @first is null 
        or id < @first
        or id > @last



    /* decode lines to paramters */

    declare @par table (ParameterName varchar(50), ParameterType varchar(50), DefaultValue varchar(50))

    declare
        @name varchar(50),
        @type varchar(50),
        @default varchar(50)

    declare c cursor for
        select line
        from @lines
    open c
    fetch next from c into @w 
    while @@FETCH_STATUS = 0 begin
        while len(@w) > 0 begin
            set @default = null

            set @w = SUBSTRING(@w, charindex('@', @w) + 1, 10000)
            set @p = CHARINDEX(',', @w)
            print 'start:' + @w
            if @p > 0 begin
                set @t = SUBSTRING(@w, 1, @p - 1)
                set @w = LTrim(RTrim(SUBSTRING(@w, @p + 1, 10000)))
            end else begin
                set @p = patindex('% as%', @w)
                if @p > 0 
                    set @t = SUBSTRING(@w, 1, @p - 1)
                else 
                    set @t = @w
                set @w = ''
            end

            print 'T=' + @t
            set @p = charindex(' ', @t) 
            if @p = 0
                print 'NameError:' + @t + ' ->' + cast(@p as varchar)
            set @name = SUBSTRING(@t, 1, @p - 1)
            set @t = SUBSTRING(@t, @p + 1, 10000)

            set @p = CHARINDEX('=', @t)
            if @p > 0 begin
                set @default = Replace(LTrim(RTrim(SUBSTRING(@t, @p + 1, 10000))), '''', '')
                set @t = SUBSTRING(@t, 1, @p - 1)
            end 

            set @p = CHARINDEX('(', @t)
            if @p > 0 
                set @type = LTrim(RTrim(SUBSTRING(@t, 1, @p - 1)))
            else
                set @type = LTrim(RTrim(@t))

            insert @par (ParameterName, ParameterType, DefaultValue)
            values(@name, @type, @default)
        end--while len(@w) > 0

        fetch next from c into @w 
    end
    close c
    deallocate c

    select *
    from @par

Answer 2

MS SQL stores default settings only for CLR stored procedures and functions, so only way in this case is parse the object definition. To run the example, you can create a blank stored procedure, or take any other.

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

This query return list of default values for stored procedure:

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

And by this query, you can know if the stored procedure contains any default values​​:

DECLARE @name SYSNAME = 'dbo.usp_test1'

IF EXISTS(
    SELECT 1
    FROM (
        SELECT 
              sm2.[object_id]
            , tokens = SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens)) 
        FROM (
            SELECT 
                  sm.[object_id]
                , 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 = @name
        ) sm2
    ) sm3
    WHERE sm3.tokens LIKE '%=%'
) PRINT @name + ' have default values'

Answer 3

WITH t1 AS
(
SELECT 
   SCHEMA_NAME(SCHEMA_ID) AS [Schema]
  ,SO.name  
  , SO.type
  ,SO.Type_Desc AS [ObjectType (UDF/SP)]
  ,P.parameter_id AS [ParameterID]
  ,P.name AS [ParameterName]
  ,P.user_type_id
  /*,P.scale
  ,P.precision
  ,P.max_length
  */
  ,TYPE_NAME(P.user_type_id) AS [ParameterDataType]
 -- ,P.max_length AS [ParameterMaxBytes]
  ,CASE WHEN user_type_id = 231 THEN max_length / 2 ELSE NULL END nvarcharsize
  ,P.is_output AS [IsOutPutParameter]
  ,ltrim(rtrim(REPLACE(LEFT( OBJECT_DEFINITION (p.object_id),90000),'CREATE PROCEDURE ',''))) ProcDef
  
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID AND SO.type = 'P'
AND SO.Name NOT LIKE 'Report%' 
AND SO.Name NOT LIKE '!_%' ESCAPE '!'
)
, T2 AS
(
select 
--ProcDef 
LEFT(ProcDef,patindex('%AS%BEGIN%',ProcDef)-2) ProcedureDef
,
name Procname,
ParameterName,
ParameterID,
ParameterDataType,
nvarcharsize
from t1 
)
, T3 AS
(
select
substring(ProcedureDef,charindex(ParameterName,ProcedureDef) + len(parametername) + len(ParameterDataType) + 2,12) parampos
,
* from t2 where procname like 'MyProcName'
)
, T4 AS
(
select
CHARINDEX('=',parampos) haseq,
replace(replace(replace(substring(parampos,CHARINDEX('=',parampos),100),' ',''),'=',''),',','') valdef
,
* from t3
)
, t5 as
(
select
CASE WHEN haseq > 0 THEN valdef else '' end defaultvalue,
* from t4 
)
select * from t5 
ORDER BY  Procname, parameterid
GO

Answer 4

There is much more simple and correct way to get set of all in and out parameters of SP and functions, please try following statement. (source is http://www.mssqltips.com/sqlservertip/1669/generate-a-parameter-list-for-all-sql-server-stored-procedures-and-functions/)

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.Name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
PM.Parameter_ID AS [ParameterID],
case 
when pm.system_type_id = pm.user_type_id then 'system_type'
else 'user_type'
end as [TypeDescr],
CASE
WHEN PM.Parameter_ID = 0 THEN 'Returns'
ELSE PM.Name
END AS [ParameterName],
'['+TYPE_NAME(PM.User_Type_ID)+']' AS [ParameterDataType],
CASE 
WHEN TYPE_NAME(PM.User_Type_ID) IN ('float', 'uniqueidentifier', 'datetime', 'bit', 'bigint', 'int', 'image', 'money', 'xml', 'varbinary', 'tinyint', 'text', 'ntext', 'smallint', 'smallmoney') THEN ''
WHEN TYPE_NAME(PM.User_Type_ID) IN ('decimal', 'numeric') THEN '(' + CAST( Precision AS VARCHAR(4) ) + ', ' + CAST( Scale AS VARCHAR(4)) + ')'
ELSE 
case 
when PM.Max_Length <> -1 then '('+CAST( PM.Max_Length AS VARCHAR(4))+')'
when (TYPE_NAME(PM.User_Type_ID) = 'xml') or (pm.system_type_id <> pm.user_type_id) then ''
else '(max)' 
end
END AS [Size],
CASE 
WHEN PM.Is_Output = 1 THEN 'Output'
ELSE 'Input'
END AS [Direction]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS PM ON SO.OBJECT_ID = PM.OBJECT_ID
WHERE TYPE IN ('P','FN')
ORDER BY [Schema], SO.Name, PM.parameter_id