sp_MSrepl_enumsubscriptions (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_MSrepl_enumsubscriptions(nvarchar @publication
, nvarchar @publisher
, bit @reserved)

MetaData:

   
--   
--  Name:  
--  sp_MSrepl_enumsubscriptions  
--   
--  Description:  
--   Enumerate subscriptions on a database  
--   
--  Returns:  
--   0 == Failed  
--   1 == Succeed  
--   
--  Security:  
--   public, db_owner check  
--   Requires Certificate signature for catalog access  
--   
--  Notes:  
--   Used by the UI to generate a list of subscriptions  
--   
CREATE PROCEDURE sys.sp_MSrepl_enumsubscriptions   
(  
    @publication sysname,  
    @publisher sysname = NULL ,  
    @reserved bit = 0   --  Set to 1 when used by UI  
)  
AS  
BEGIN  
    set nocount on  
    DECLARE @retcode int  
                ,@dbname sysname  
                ,@hasadminaccess bit  
                ,@login_name sysname  
                ,@category int  
                ,@fpublished bit  
              
    --   
    --  Security Check.  
    --  Part of the query to gather the list of publications uses security context  
    --  Optimization done for syadmin or dbowner  
    --   
    select @dbname = db_name()  
            ,@hasadminaccess = case when (is_member('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end  
            ,@login_name = suser_sname(suser_sid())  
          
    --  Verify database is published  
    --  Have put the code from fn_MSrepl_ispublished inline for faster processing  
    --  If fn_MSrepl_ispublished() is updated - this code block should be updated as well  
    SELECT @category = category  
    FROM master.sys.sysdatabases  
    WHERE name = @dbname   
  
    IF (@category IS NOT NULL)  
    BEGIN  
        --  We have entry for this db_name in sysdatabases  
        --  Is this database a distributor?  
        IF (@category & 16 = 16)  
        BEGIN  
            --  DB is a distributor - is it used for HREPL publisher?  
            IF OBJECT_ID(N'msdb.dbo.MSdistpublishers') IS NOT NULL  
            BEGIN  
                IF EXISTS  
                (  
                    SELECT name  
                    FROM msdb.dbo.MSdistpublishers  
                    WHERE distribution_db = @dbname   
                        AND publisher_type != N'MSSQLSERVER'  
                )  
                BEGIN  
                    SELECT @fpublished = 1  
                END  
            END  
        END  
        ELSE  
        BEGIN  
            --  Check if this database is a transactional or merge publisher  
            IF (@category & 1 = 1 OR @category & 4 = 4)  
            BEGIN  
                SELECT @fpublished = 1  
            END  
        END  
    END --  @category not null  
    --   
    --  If the database is not published - return  
    --   
    IF (@fpublished = 0)   
        RETURN (0)  
    --   
    --  set publisher  
    --   
    if (@publisher IS NULL)  
        select @publisher = publishingservername()  
    --   
    --  Create temp table if needed  
    --   
    if (@reserved = 0)  
    begin  
        create table #tmp_pubsubscriptions  
        (  
            publisher sysname not null,  
            publisher_db sysname not null,  
            publisher_type sysname not null,  
            publication sysname not null,  
            publication_type int not null,  
            subscription_name nvarchar(258) not null,  
            subscriber sysname not null,  
            subscriber_db sysname not null,  
            subscription_type int not null,  
            priority float(8) not null default 0.0  
        )  
    end  
    --  Get snapshot or transactional subscriptions  
    if (object_id(N'dbo.syssubscriptions') is not null)  
    BEGIN  
        IF (@category & 16 != 16)  
        BEGIN  
            --  SQL publication db  
            INSERT INTO #tmp_pubsubscriptions  
            (  
                publisher,  
                publisher_db,  
                publisher_type,  
                publication,  
                publication_type,  
                subscription_name,  
                subscriber,  
                subscriber_db,  
                subscription_type  
            )  
            SELECT DISTINCT  
                @publisher,  
                @dbname,  
                N'MSSQLSERVER',  
                pub.name,  
                pub.repl_freq,  
                sub.srvname collate database_default + N':' + sub.dest_db,  
                sub.srvname,  
                sub.dest_db,  
                sub.subscription_type  
            FROM dbo.syssubscriptions as sub  
                join dbo.sysextendedarticlesview as art on sub.artid = art.artid  
                join dbo.syspublications as pub on art.pubid = pub.pubid  
            WHERE pub.name = @publication  
              AND (@hasadminaccess = 1 OR sub.login_name = @login_name)  
              and sub.srvname is not null and len(sub.srvname) > 0  
              --  GROUP BY srv.name, sub.dest_db, pub.name, pub.repl_freq, sub.subscription_type  
        END  
        ELSE  
        BEGIN  
            --  Heterogeneous subscriptions  
            INSERT INTO #tmp_pubsubscriptions  
            (  
                publisher,  
                publisher_db,  
                publisher_type,  
                publication,  
                publication_type,  
                subscription_name,  
                subscriber,  
                subscriber_db,  
                subscription_type  
            )  
            SELECT DISTINCT  
                pubsrv.name,  
                pubsrv.name,  
                msd.publisher_type,  
                pub.name,  
                pub.repl_freq,  
                subsrv.name collate database_default + N':' + sub.dest_db,  
                subsrv.name,  
                sub.dest_db,  
                sub.subscription_type  
            FROM dbo.syssubscriptions as sub  
                join sys.servers as subsrv on sub.srvid = subsrv.server_id  
                join dbo.sysarticles as art on sub.artid = art.artid  
                join dbo.syspublications as pub on art.pubid = pub.pubid  
                join dbo.MSpublications as msp on pub.pubid = msp.publication_id  
                join sys.servers as pubsrv on pubsrv.server_id = msp.publisher_id  
                join msdb.dbo.MSdistpublishers as msd on msd.name = pubsrv.name  
            WHERE pub.name = @publication  
              AND pubsrv.name = @publisher  
              AND (@hasadminaccess = 1 OR sub.login_name = @login_name)  
        END  
    END  
  
    --  Get merge subscriptions  
    if (object_id(N'dbo.sysmergesubscriptions') is not null)  
    BEGIN  
        INSERT INTO #tmp_pubsubscriptions  
        (  
            publisher,  
            publisher_db,  
            publisher_type,  
            publication,  
            publication_type,  
            subscription_name,  
            subscriber,  
            subscriber_db,  
            subscription_type,  
            priority  
        )  
        SELECT DISTINCT  
            pub.publisher,  
            pub.publisher_db,  
            N'MSSQLSERVER',  
            pub.name,  
            2,  
            sub.subscriber_server + N':' + sub.db_name,  
            sub.subscriber_server,  
            sub.db_name,  
            sub.subscription_type,  
            sub.priority  
        FROM dbo.sysmergesubscriptions as sub  
            join dbo.sysmergepublications as pub on pub.pubid = sub.pubid  
            join dbo.MSmerge_replinfo as rep on rep.repid = sub.subid  
        WHERE sub.status != 2  
            AND sub.subscriber_type <> 3  
            AND sub.subid  != sub.pubid  
            AND pub.name = @publication  
            AND (@hasadminaccess = 1 OR rep.login_name = @login_name)  
    END  
  
    --  Return result set if needed  
    if (@reserved = 0)  
    begin  
        SELECT *  
        FROM #tmp_pubsubscriptions  
        ORDER BY publisher, subscriber, subscriber_db  
    end  
    --  all done  
    RETURN (0)  
END