USE masterGOSET NOCOUNT ONDECLARE @pubinfo_for_drop TABLE(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,publication_id int,publication SYSNAME,pub_server SYSNAME, pub_db SYSNAME, pub_schema SYSNAME, pub_object SYSNAME, sub_server SYSNAME,sub_db SYSNAME, sub_schema SYSNAME,sub_object SYSNAME)DECLARE @execsql TABLE(id INT IDENTITY(1,1) PRIMARY KEY,runsql VARCHAR(2000))--解析publisher;WITH PUBLISHERS AS( SELECT publisher=N'Server.DB.dbo.TB' ,subscriber_server = NULL ,subscriber_database =NULL,subscriber_schema = N'dbo',subscriber_object =NULLUNION ALL SELECT publisher=N'Server.DB.dbo.mytb,sam' ,subscriber_server = NULL ,subscriber_database =NULL,subscriber_schema = N'dbo',subscriber_object =NULL),PUB_INFOAS(SELECT TA.pub_server,pub_db,pub_schema,T.C.value('.[1]','sysname') pub_object,TA.sub_server,TA.sub_db,TA.sub_schema,TA.sub_objectFROM(SELECT PARSENAME(PUBLISHERS.publisher,4) AS pub_server,PARSENAME(PUBLISHERS.publisher,3) AS pub_db,PARSENAME(PUBLISHERS.publisher,2) AS pub_schema, CAST(N'<value>'+REPLACE(PARSENAME(PUBLISHERS.publisher,1),N',',N'</value><value>')+N'</value>' AS XML ) TBXML,subscriber_server AS sub_server,subscriber_database AS sub_db,subscriber_schema AS sub_schema,subscriber_object AS sub_objectFROM PUBLISHERS ) TACROSS APPLYTA.TBXML.nodes('value') AS T(C)) ,PUB_INFO_2AS(SELECTpub.pub_server, pub.pub_db, pub.pub_schema, pub.pub_object,pub.sub_server,pub.sub_db AS sub_db,ISNULL(CAST(pub.sub_schema AS SYSNAME),CAST(pub.pub_schema AS SYSNAME)) AS sub_schema,ISNULL(CAST(pub.sub_object AS SYSNAME),CAST(pub.pub_object AS SYSNAME)) AS sub_objectFROM PUB_INFO AS pub),PUN_INFO_ALLAS(SELECT PUB.publication_id,PUB.publication, @@SERVERNAME pub_server,ART.publisher_db pub_db,ART.source_owner pub_schema,ART.source_object pub_object,SER.name sub_server,SUB.subscriber_db sub_db,ISNULL(CAST(ART.destination_owner AS SYSNAME),CAST(ART.source_owner AS SYSNAME)) sub_schema,ART.destination_object sub_objectFROM distribution.dbo.MSarticles ARTINNER JOIN distribution.dbo.MSpublications PUBON ART.publication_id = PUB.publication_idINNER JOIN distribution.dbo.MSsubscriptions SUBON SUB.publication_id = PUB.publication_idAND SUB.article_id = ART.article_idINNER JOIN sys.servers SERON SER.server_id = SUB.subscriber_id)INSERT INTO @pubinfo_for_dropSELECT PALL.publication_id,PALL.publication,P2.pub_server, P2.pub_db, P2.pub_schema, P2.pub_object,sub_server=CASE ISNULL(CAST(P2.sub_server AS SYSNAME),'{_NOTSUBSERVER}')WHEN '{_NOTSUBSERVER}' THEN CAST(PALL.sub_server AS SYSNAME)ELSE CAST(P2.sub_server AS SYSNAME)END,sub_db=CASE ISNULL(CAST(P2.sub_server AS SYSNAME),'{_NOTSUBSERVER}')WHEN '{_NOTSUBSERVER}' THEN CAST(PALL.sub_db AS SYSNAME)ELSE ISNULL(CAST(P2.sub_db AS SYSNAME),CAST(PALL.sub_db AS SYSNAME)) END,P2.sub_schema, P2.sub_objectFROM PUB_INFO_2 P2INNER JOIN PUN_INFO_ALL PALLON P2.pub_server=PALL.pub_serverAND P2.pub_db=PALL.pub_dbAND P2.pub_schema=PALL.pub_schemaAND P2.pub_object=PALL.pub_object--AND P2.sub_db=PALL.sub_dbAND ( P2.sub_server IS NULL OR (P2.sub_server IS NOT NULL AND P2.sub_server=PALL.sub_server))ORDER BY PALL.publication_id,P2.pub_dbSELECT * FROM @pubinfo_for_dropDECLARE @pubinfo TABLE(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,publication_id int,publication SYSNAME,pub_server SYSNAME, pub_db SYSNAME, pub_schema SYSNAME, pub_object SYSNAME, sub_server SYSNAME,sub_db SYSNAME, sub_schema SYSNAME,sub_article SYSNAME)INSERT INTO @pubinfoSELECT PUB.publication_id,PUB.publication, @@SERVERNAME pub_server,ART.publisher_db pub_db,ART.source_owner pub_schema,ART.source_object pub_object,SER.name sub_server,SUB.subscriber_db sub_db,ISNULL(CAST(ART.destination_owner AS SYSNAME),CAST(ART.source_owner AS SYSNAME)) sub_schema,ART.articleFROM distribution.dbo.MSarticles ARTINNER JOIN distribution.dbo.MSpublications PUBON ART.publication_id = PUB.publication_idINNER JOIN distribution.dbo.MSsubscriptions SUBON SUB.publication_id = PUB.publication_idAND SUB.article_id = ART.article_idINNER JOIN sys.servers SERON SER.server_id = SUB.subscriber_id--SELECT * FROM @pubinfoDECLARE @dropsubscription VARCHAR(2000),@droparticle VARCHAR(2000),@droppublication VARCHAR(2000),@dropsubscription_in VARCHAR(2000),@droparticle_in VARCHAR(2000),@droppublication_in VARCHAR(2000)SELECT @dropsubscription = 'use {db}exec sp_dropsubscription @publication = N''{publication_name}'', @subscriber = N''{sub_server}'', @destination_db = N''{sub_db}'', @article = N''{sub_object}''',@droparticle='use {db}exec sp_droparticle @publication = N''{publication_name}'', @article = N''{sub_object}'', @force_invalidate_snapshot = 1',@droppublication='use {db}exec sp_droppublication @publication = N''{publication_name}'''DECLARE @db AS SYSNAME,@publication_id INT,@publication AS SYSNAME,@pub_db AS SYSNAME,@sub_server AS SYSNAME,@sub_db AS SYSNAME,@sub_object AS SYSNAMEDECLARE @id AS INT=-1WHILE(1=1)BEGINIF NOT EXISTS( SELECT TOP(1) 1 FROM @pubinfo_for_drop)BREAKSELECT TOP(1) @id=id, @publication_id=publication_id, @publication=publication, @pub_db=pub_db,@sub_server=sub_server, @sub_db=sub_db, @sub_object=sub_objectFROM @pubinfo_for_drop--1--同一个表对象被多个server订阅，删除部分server的订阅IF EXISTS(SELECT TOP(1) 1 FROM @pubinfo AS INFOWHERE INFO.publication_id=@publication_idAND INFO.sub_article=@sub_objectAND INFO.sub_server <> @sub_server)BEGINSELECT @dropsubscription_in=REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@dropsubscription,'{db}',@pub_db),'{publication_name}',@publication),'{sub_server}',@sub_server),'{sub_db}',@sub_db),'{sub_object}',@sub_object)DELETE TOP(1) FROM @pubinfo WHERE publication_id=@publication_idAND sub_server=@sub_serverAND sub_db=@sub_dbAND sub_article=@sub_objectPRINT '--************'PRINT @dropsubscription_in --TESTGOTO NEXTSTEPINWHILEEND--在同一个publication里还存在其他的订阅对象 IF EXISTS(SELECT TOP(1) 1 FROM @pubinfo AS INFOWHERE INFO.publication_id=@publication_idAND INFO.sub_server=@sub_serverAND INFO.sub_db=@sub_dbAND INFO.sub_article<>@sub_object)BEGINSELECT @dropsubscription_in=REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@dropsubscription,'{db}',@pub_db),'{publication_name}',@publication),'{sub_server}',@sub_server),'{sub_db}',@sub_db),'{sub_object}',@sub_object)SELECT @droparticle_in=REPLACE(REPLACE(REPLACE(@droparticle,'{db}',@pub_db),'{publication_name}',@publication),'{sub_object}',@sub_object)DELETE TOP(1) FROM @pubinfo WHERE publication_id=@publication_idAND sub_server=@sub_serverAND sub_db=@sub_dbAND sub_article=@sub_objectPRINT '--************'PRINT @dropsubscription_in --TESTPRINT @droparticle_inGOTO NEXTSTEPINWHILEEND--在该发布中没有其他订阅对象， IF NOT EXISTS(SELECT TOP(1) 1 FROM @pubinfo AS INFOWHERE INFO.publication_id=@publication_idAND INFO.sub_server=@sub_serverAND INFO.sub_db=@sub_dbAND INFO.sub_article<>@sub_object)BEGINSELECT @dropsubscription_in=REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@dropsubscription,'{db}',@pub_db),'{publication_name}',@publication),'{sub_server}',@sub_server),'{sub_db}',@sub_db),'{sub_object}',@sub_object)SELECT @droparticle_in=REPLACE(REPLACE(REPLACE(@droparticle,'{db}',@pub_db),'{publication_name}',@publication),'{sub_object}',@sub_object)SELECT @droppublication_in=REPLACE(REPLACE(@droppublication,'{db}',@pub_db),'{publication_name}',@publication)DELETE TOP(1) FROM @pubinfo WHERE publication_id=@publication_idAND sub_server=@sub_serverAND sub_db=@sub_dbAND sub_article=@sub_objectPRINT '--************'PRINT @dropsubscription_in --TESTPRINT @droparticle_inPRINT @droppublication_inGOTO NEXTSTEPINWHILEENDNEXTSTEPINWHILE:--SELECT @id,@publication_id,@publication,@subscriber,@destination_db,@article --TESTDELETE TOP(1) FROM @pubinfo_for_dropWHERE id=@idEND
