Get Default Values of Parameters of Stored Procedures

If you are dealing and quering on the
schema of your database, you probably need this script;

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--
=============================================

-- Author:        Atif

-- Create date:24-May-2010

-- Description:    Get Default Value of a Parameter of SP or Function

--
=============================================

ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]

(    

    @pSPName varchar(1000)='',

    @pParameterName varchar(100),

    @pDataType varchar(100),

    @pType bit=0 --0 for Stored Procedure and 1 for Function

)

RETURNS varchar(1000)

AS

BEGIN    

    Declare @pOutPut varchar(1000)

    Declare @vStartPosition int

    Declare @vStartPosition2 int    

    Declare @vStartPosition3 int

    Declare @vStartPosition4 int

    Declare @vStartPosition5 int

    Declare @vSPText varchar(max)

    Declare @vSPText2 varchar(max)

-- Get the text form syscomments (first 4000 characters if length is > 4000)

    if @pType = 0

    begin

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 1)

        Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)

    end

    else

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 0)

      Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE FUNCTION',@vSPtext),4000)

    if IsNull(@vSPtext,'') = ''

    begin

        -- Exit if SP Name Not found in syscomments....

        Select @pOutPut = ''

        RETURN @pOutPut

    end 

    Set @pOutPut = ''
    While 1=1
    Begin
        -- Get the position of the parameter definition.
        Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
        -- Check if parameter exists
        if @vStartPosition > 0
        begin
            -- Get the Definition String
            select @vSPText=RIGHT(@vSPText, DataLength(@vSPText)-(@vStartPosition -1))

            -- Get the string breaker

            if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)

                or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText)

      > 0)
            begin                
                Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1

                Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1

                if @vStartPosition <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition = 10000000

                if @vStartPosition2 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition2 = 10000000

                if @vStartPosition3 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition3 = 10000000

                if @vStartPosition4 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition4 = 10000000

                if @vStartPosition5 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition5 = 10000000

                Select Top 1 @vStartPosition = [value]
                from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10))
                                + ',' + Cast(@vStartPosition3 as varchar(10))

                                + ',' + Cast(@vStartPosition4 as varchar(10))

                                + ',' + Cast(@vStartPosition5 as varchar(10)) ,',')    

                order by Cast([value] as int)

            end

            else

            begin

                -- SP text must atleast have AS to break the parameter definition string

                Set @vStartPosition = CharIndex('AS',@vSPText) - 1

            end             

            -- Get the specific Definition String

            Set @vSPText2 = Left(@vSPText,@vStartPosition)

            -- Check if you got the right one by data type...            

            if CharIndex(@pDataType,@vSPText2) > 0

            begin

                --Select 'IN'

                --Select @text2

                if CharIndex('=',@vSPText2) > 0

                begin

                    -- check the default value

                    Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))

                    -- We have default value assigned here

                if Right(@pOutPut,1) = ','

                        Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)

                end

                else

                begin

                    --Set @pOutPut = 'No Default Value Defined...'

                    -- We DO NOT have default value assigned here

                    Set @pOutPut = ''

                end

                --No need to work further with this parameter

                BREAK

            end

            else

            begin   

                --Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)

                -- Cut the SP text short and loop again

                Set @vSPText = SubString(@vSPText,@vStartPosition,4000)

            end

            -- This should never be the case. Just a check....

            if Datalength(@vSPText) < Datalength(@pParameterName)

                Break

        end

        else

        begin

            --Set @pOutPut = 'Parameter Not Found...'

            -- Wrong parameter search...

            Set @pOutPut = ''

            Break

        end

    End

    Select @pOutPut = rtrim(ltrim(@pOutPut))

    RETURN @pOutPut

END

To use this function, here is the proper way;

Select a.[name],b.[name] ,dbo.fnGetParameterDefaultValue('[dbo].[YourSPName]',a.[name],b.[name],0) as defaultVal

from sys.parameters a

inner join sys.types b on b.system_type_id = a.system_type_id

where Object_id = object_id('[dbo].[YourSPName]')