sql server - 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