sp_MSdrop_publication (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_MSdrop_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @alt_snapshot_folder
, bit @cleanup_orphans)

MetaData:

 CREATE PROCEDURE sys.sp_MSdrop_publication  
(  
    @publisher sysname,  
    @publisher_db sysname,  
    @publication sysname,  
    @alt_snapshot_folder sysname = NULL,  
    @cleanup_orphans bit = 0 --  this is set when cleaning up  
)  
as  
begin  
    set nocount on  
  
    declare @publisher_id smallint  
                ,@publication_id int  
                ,@retcode int  
                ,@article sysname  
                ,@article_id int  
                ,@subscriber sysname  
                ,@subscriber_db sysname  
                ,@thirdparty_flag bit  
                ,@working_dir nvarchar(255)  
                ,@pub_dir nvarchar(255)  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
    --   
    --  security check  
    --  Has to be executed from distribution database  
    --   
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)  
    begin  
        raiserror(21482, 16, -1, 'sp_MSdrop_publication', 'distribution')  
        return (1)  
    end  
  
    --  Check if publisher is a defined as a distribution publisher in the current database  
    exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT  
    if @retcode <> 0  
    begin  
        return(1)  
    end  
  
    --  Make sure publication exists  
    select @publication_id = publication_id, @thirdparty_flag = thirdparty_flag   
        from dbo.MSpublications where publication = @publication and  
        publisher_id = @publisher_id and publisher_db = @publisher_db  
    if @publication_id is NULL  
    begin  
        --  We don't know whether or not it is a third party or not so we can not   
        --  return error.  
        --  raiserror(20026, 16, -1, @publication)  
        --  return (1)  
        return (0)  
    end  
  
    if (@cleanup_orphans = 0)  
    begin  
        --  Make sure that there are no subscriptions on the publication.  
        if exists (select * from dbo.MSsubscriptions s, dbo.MSpublications p where   
            p.publisher_id = @publisher_id and  
            p.publisher_db = @publisher_db and  
            p.publication = @publication and  
            s.publisher_id = @publisher_id and  
            s.publisher_db = @publisher_db and  
            s.publication_id = p.publication_id and  
            s.subscriber_id >= 0)               --  ignore virtual subscriptions  
        begin  
            raiserror(14005, 16, -1)  
            return(1)  
        end  
        --  No real subscriptions exist, so delete any virtual subscriptions.  
        exec sys.sp_MSdrop_subscription  
            @publisher = @publisher,  
            @publisher_db = @publisher_db,  
            @publication = @publication,  
            @subscriber = null  
    end  
    else  
    begin  
        --   
        --  cleanup existing subscriptions since we are dropping this publication  
        --   
        declare #hcsubart cursor LOCAL FAST_FORWARD FOR   
            select s.article_id, upper(ss.name collate database_default), s.subscriber_db  
            from dbo.MSsubscriptions as s   
                join dbo.MSpublications as p  
                    on s.publisher_id = p.publisher_id  
                        and s.publisher_db = p.publisher_db  
                        and s.publication_id = p.publication_id  
                        and s.subscriber_id >= 0 --  ignore virtual subscriptions  
                join sys.servers as ss  
                    on s.subscriber_id = ss.server_id  
            where p.publisher_id = @publisher_id   
                and p.publisher_db = @publisher_db   
                and p.publication = @publication   
  
        open #hcsubart  
        fetch #hcsubart into @article_id, @subscriber, @subscriber_db  
        while (@@fetch_status != -1)  
        begin  
            exec sys.sp_MSdrop_subscription  
                            @publisher = @publisher,  
                            @publisher_db = @publisher_db,  
                            @subscriber = @subscriber,  
                            @article_id = @article_id,  
                            @subscriber_db = @subscriber_db,  
                            @publication = @publication  
            fetch #hcsubart into @article_id, @subscriber, @subscriber_db  
        end  
        close #hcsubart  
        deallocate #hcsubart  
        --  delete any virtual subscriptions.  
        exec sys.sp_MSdrop_subscription  
            @publisher = @publisher,  
            @publisher_db = @publisher_db,  
            @publication = @publication,  
                  @subscriber = null  
  
    end  
  
    SELECT @working_dir = working_directory FROM msdb..MSdistpublishers  
        where UPPER(name) = UPPER(@publisher)  
  
    IF @working_dir IS NOT NULL  
    BEGIN  
        --  Remove the pub dir under UNC and FTP if it exists  
        --  Note: sp_MSreplremoveuncdir will convert unc path to local path.  
        --  This is required. Otherwise we will see 'Access denied' error.  
        SELECT @pub_dir = @working_dir + '\unc\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @working_dir + '\ftp\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @working_dir + '\unc\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @working_dir + '\ftp\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
    END  
  
    IF @alt_snapshot_folder IS NOT NULL AND RTRIM(@alt_snapshot_folder) <> N''  
    BEGIN  
  
        --  Make sure that alt_snapshot_folder is \ terminated  
        IF SUBSTRING(@alt_snapshot_folder,len(@alt_snapshot_folder),1) <> N'\'  
        BEGIN  
            SELECT @alt_snapshot_folder = @alt_snapshot_folder + N'\'  
        END  
  
        --  Remove the pub dir under UNC and FTP if it exists  
        --  Note: sp_MSreplremoveuncdir will convert unc path to local path.  
        --  This is required. Otherwise we will see 'Access denied' error.  
        SELECT @pub_dir = @alt_snapshot_folder + 'unc\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @alt_snapshot_folder + 'ftp\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @alt_snapshot_folder + 'unc\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
        SELECT @pub_dir = @alt_snapshot_folder + 'ftp\' +   
                    fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default  
        exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1  
        if @retcode <> 0 or @@error <> 0  
            return(1)  
  
    END  
      
    begin tran  
    save tran MSdrop_publication  
  
    --  Delete all articles if a third party publication  
    if @thirdparty_flag = 1   
    begin  
        --  Delete all articles in the publication  
        declare hCarticles CURSOR LOCAL FAST_FORWARD FOR select article from MSarticles where   
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication_id =   
                (select publication_id from dbo.MSpublications where   
                    publisher_id = @publisher_id and  
                    publisher_db = @publisher_db and  
                    publication = @publication)  
        open hCarticles  
        fetch hCarticles into @article  
        while (@@fetch_status <> -1)  
        begin  
            exec @retcode = sys.sp_MSdrop_article @publisher, @publisher_db, @publication, @article  
            if @retcode != 0 or @@error != 0  
            begin  
                close hCarticles  
                deallocate hCarticles  
                goto UNDO  
            end  
                  
            fetch hCarticles into @article  
        end  
        close hCarticles  
        deallocate hCarticles  
    end  
    --   
    --  remove threshold entries for this publication  
    --   
    delete dbo.MSpublicationthresholds   
        where publication_id = @publication_id  
    if @@error <> 0  
        goto UNDO  
    --   
    --  remove entry from dbo.MSpublications  
    --   
    delete from dbo.MSpublications where   
        publisher_id = @publisher_id and  
        publisher_db = @publisher_db and  
        publication = @publication  
    if @@error <> 0  
    begin  
        raiserror (14006, 16, -1)  
        goto UNDO  
    end  
  
    --  Drop snapshot agent  
    exec @retcode = sys.sp_MSdrop_snapshot_agent  
            @publisher = @publisher,  
            @publisher_db = @publisher_db,  
            @publication = @publication  
    if @@ERROR<> 0 or @retcode <> 0  
        goto UNDO  
  
    --  delete cache for this agent  
    delete MScached_peer_lsns   
            where agent_id in (select id  
                                from MSdistribution_agents   
                                where publisher_id = @publisher_id  
                                    and publisher_db = @publisher_db   
                                    and publication = @publication)  
    if @@ERROR<> 0  
        goto UNDO  
      
    --  Delete anonymous agents  
    delete MSdistribution_agents where  
        publisher_id = @publisher_id and  
        publisher_db = @publisher_db and  
        publication = @publication  
    if @@ERROR<> 0 or @retcode <> 0  
        goto UNDO  
  
delete from dbo.MSmerge_subscriptions  
where       publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication_id = @publication_id  
  
  
    delete dbo.MSmerge_articlehistory  
        from dbo.MSmerge_articlehistory arthist join dbo.MSmerge_sessions sess  
        on arthist.session_id=sess.session_id  
        where sess.agent_id in   
        (select id from dbo.MSmerge_agents where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication = @publication)  
    IF @@ERROR <> 0   
        GOTO UNDO  
          
    delete dbo.MSmerge_history  
        from dbo.MSmerge_history hist join dbo.MSmerge_sessions sess  
        on hist.session_id=sess.session_id  
        where sess.agent_id in   
        (select id from dbo.MSmerge_agents where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication = @publication)  
    IF @@ERROR <> 0   
        GOTO UNDO  
          
    delete dbo.MSrepl_errors  
        from dbo.MSrepl_errors errs join dbo.MSmerge_sessions sess  
        on errs.session_id=sess.session_id  
        where sess.agent_id in   
        (select id from dbo.MSmerge_agents where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication = @publication)  
    IF @@ERROR <> 0   
        GOTO UNDO  
          
    --  delete sessions entries   
    delete dbo.MSmerge_sessions where agent_id in   
        (select id from dbo.MSmerge_agents where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication = @publication)  
              
    delete dbo.MSmerge_agents where  
        publisher_id = @publisher_id and  
        publisher_db = @publisher_db and  
        publication = @publication  
    if @@ERROR<> 0 or @retcode <> 0  
        goto UNDO  
  
    --  Cleanup publication access list table  
    delete dbo.MSpublication_access where  
        publication_id = @publication_id  
    if @@ERROR<> 0 or @retcode <> 0  
        goto UNDO  
  
    --  cleanup identity range allocation history information  
    delete dbo.MSmerge_identity_range_allocations   
        where publisher_id = @publisher_id and  
              publisher_db = @publisher_db and  
              publication = @publication  
    if @@ERROR<> 0 or @retcode <> 0  
        goto UNDO  
                
    --  Remove publisher_id, publisher_db pair if no other publication is using it.  
    if not exists (select * from msdb.dbo.MSdistpublishers d, master.dbo.sysservers s  
    where s.srvid = @publisher_id  
    and upper(s.srvname) = upper(d.name) collate database_default  
    and upper(d.publisher_type) LIKE 'ORACLE%' )  
    and  
       not exists (select * from dbo.MSpublications where publisher_id = @publisher_id and  
        publisher_db = @publisher_db)  
    begin  
        declare @publisher_database_id int  
  
        select @publisher_database_id = id from MSpublisher_databases where   
            publisher_id = @publisher_id and   
            publisher_db = @publisher_db  
  
        delete from MSrepl_backup_lsns where   
            publisher_database_id = @publisher_database_id  
  
        delete from MSpublisher_databases where   
            publisher_id = @publisher_id and publisher_db = @publisher_db  
        if @@error <> 0  
            goto UNDO  
  
        --  Cleaning up MSrepl_originators  
        delete MSrepl_originators where  
            publisher_database_id = @publisher_database_id  
        if @@error <> 0  
            goto UNDO  
    end  
    --   
    --  commit all the work  
    --   
    commit tran  
    --   
    --  all done  
    --   
    return 0  
  
UNDO:  
    if @@trancount > 0  
    begin  
        rollback tran MSdrop_publication  
        commit tran  
    end  
    return (1)  
end