Get default values of parameters in stored procedures

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]')

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 fro syscomments (first 4000 characters if length of SP 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