sp_stored_procedures (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_stored_procedures(nvarchar @sp_name
, nvarchar @sp_owner
, nvarchar @sp_qualifier
, bit @fUsePattern)

MetaData:

   
create procedure sys.sp_stored_procedures  
(  
    @sp_name            nvarchar(390) = null, --  Wildcard pattern matching is supported.  
    @sp_owner           nvarchar(384) = null, --  Wildcard pattern matching is supported.  
    @sp_qualifier       sysname = null,  
    @fUsePattern        bit = 1 --  To allow users to explicitly disable all pattern matching.  
)  
as  
    declare @full_sp_name    nvarchar(775) --  384 + 1 + 390  
    declare @sp_id           int  
    declare @use_system      bit  
  
    if @sp_qualifier is not null  
    begin  
        if db_name() <> @sp_qualifier  
        begin  
            if @sp_qualifier = ''  
            begin  
                --  in this case, we need to return an empty result set  
                --  because the user has requested a database with an empty name  
                select @sp_name = ''  
                select @sp_owner = ''  
            end  
            else  
            begin   --  If qualifier doesn't match current database  
                raiserror (15250, -1,-1)  
                return  
            end  
        end  
    end  
  
    if @sp_name = '%'  
        select @sp_name = null  
    if @sp_owner = '%'  
        select @sp_owner = null  
  
    if @sp_name is not null  
    begin  
        if (@sp_owner is null) and (charindex('%', @sp_name) = 0)  
        begin  
            if exists (  
                        select *  
                        from  
                            sys.spt_all_procedures pro  
                        where  
                            pro.schema_id = schema_id() and  
                            pro.name = @sp_name  
                      )  
            begin  
                select @sp_owner = schema_name()  
            end  
        end  
    end  
  
    select @full_sp_name = isnull(quotename(@sp_owner), '') + '.' + isnull(quotename(@sp_name), '')  
    select @sp_id = object_id(@full_sp_name)  
  
    if (@fUsePattern = 1) --  Does the user want it?  
    begin  
        if ((isnull(charindex('%', @full_sp_name),0) = 0) and  
            (isnull(charindex('_', @full_sp_name),0) = 0) and  
            (@sp_id <> 0))  
        begin  
            select @fUsePattern = 0 --  not a single wild char, so go the fast way.  
        end  
  
        select @use_system = 0  
        if (@sp_name is null or @sp_name like 'fn_%' or @sp_name like 'sp_%' or @sp_name like 'xp_%')  
        begin  
            select @use_system = 1  
        end  
    end  
  
    if @fUsePattern = 0  
    begin  
        --  --  Debug output, do not remove it.  
        print '-- -- -- -- -- -- *'  
        print 'No pattern matching.'  
        print @fUsePattern  
        print isnull(@full_sp_name, '@full_sp_name = null')  
        print isnull(@sp_name, '@sp_name = null')  
        print isnull(@sp_owner, '@sp_owner = null')  
        print '-- -- -- -- -- -- *'  
         --   
        select  
            PROCEDURE_QUALIFIER = convert(sysname,db_name()),  
            PROCEDURE_OWNER     = convert(sysname,schema_name(pro.schema_id)),  
            PROCEDURE_NAME      = convert(nvarchar(134),pro.name + ';' + convert(nvarchar(10), pro.procedure_number)),  
            NUM_INPUT_PARAMS    = convert(int, -1), --  reserved  
            NUM_OUTPUT_PARAMS   = convert(int, -1), --  reserved  
            NUM_RESULT_SETS     = convert(int, -1), --  reserved  
            REMARKS             = convert(varchar(254),null),   --  Remarks are NULL  
            PROCEDURE_TYPE      = convert(smallint, 2) --  SQL_PT_FUNCTION  
        from  
            sys.spt_all_procedures pro  
        where  
            @sp_name is null or pro.object_id = @sp_id  
        order by 1, 2, 3  
    end  
    else  
    begin  
        --  --  Debug output, do not remove it.  
        print '-- -- -- -- -- -- *'  
        print 'THERE IS pattern matching!'  
        print @fUsePattern  
        print isnull(@full_sp_name, '@full_sp_name = null')  
        print isnull(@sp_name, '@sp_name = null')  
        print isnull(@sp_owner, '@sp_owner = null')  
        print '-- -- -- -- -- -- *'  
         --   
        if @sp_name is null  
            select @sp_name = '%'  
        if @sp_owner is null  
            select @sp_owner = '%'  
  
        --  For user procedures  
        select  
            PROCEDURE_QUALIFIER = convert(sysname,db_name()),  
            PROCEDURE_OWNER     = convert(sysname,schema_name(o.schema_id)),  
            PROCEDURE_NAME      = convert(nvarchar(134),o.name + ';' + convert(nvarchar(10), case o.type when 'P' then p.procedure_number else 0 end)),  
            NUM_INPUT_PARAMS    = convert(int, -1), --  reserved  
            NUM_OUTPUT_PARAMS   = convert(int, -1), --  reserved  
            NUM_RESULT_SETS     = convert(int, -1), --  reserved  
            REMARKS             = convert(varchar(254),null),   --  Remarks are NULL  
            PROCEDURE_TYPE      = convert(smallint, 2) --  SQL_PT_FUNCTION  
        from  
            sys.objects o join  
            sys.numbered_procedures$ p on  
            (  
                p.object_id = o.object_id  
            )  
        where  
            o.name like @sp_name and  
            (@sp_owner = '%' or schema_name(o.schema_id) like @sp_owner) and  
            o.type in ('P', 'FN', 'TF', 'IF', 'PC') --  Object type of Procedure  
  
        union all  
  
        --  For system procedures.  
        --  We don't have info about numbered system procedures(sp_ddopen;2, etc.)  
        select  
            PROCEDURE_QUALIFIER = convert(sysname,db_name()),  
            PROCEDURE_OWNER     = convert(sysname,'sys'),  
            PROCEDURE_NAME      = convert(nvarchar(134),o.name + ';' + convert(nvarchar(10), case o.type when 'P' then 1 else 0 end)),  
            NUM_INPUT_PARAMS    = convert(int, -1), --  reserved  
            NUM_OUTPUT_PARAMS   = convert(int, -1), --  reserved  
            NUM_RESULT_SETS     = convert(int, -1), --  reserved  
            REMARKS             = convert(varchar(254),null),   --  Remarks are NULL  
            PROCEDURE_TYPE      = convert(smallint, 2) --  SQL_PT_FUNCTION  
        from  
            sys.system_objects o  
        where  
            @use_system = 1 and  
            o.name like @sp_name and  
            (@sp_owner = '%' or 'sys' like @sp_owner) and  
            o.type in ('P', 'FN', 'TF', 'IF', 'PC') --  Object type of Procedure  
        order by 2, 3  
    end