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_dropdistributor(bit @no_checks, bit @ignore_distributor)
MetaData:
create procedure sys.sp_dropdistributor
(
@no_checks bit = 0,
@ignore_distributor bit = 0
)
AS
SET NOCOUNT ON
--
-- Declarations.
--
DECLARE @retcode int
DECLARE @distributor sysname
DECLARE @agentname nvarchar(100)
DECLARE @distbit int
DECLARE @distribdb sysname
DECLARE @foundSubscriber int
DECLARE @proc nvarchar(255)
declare @optname sysname
declare @name sysname
DECLARE @transpublishdb_bit int
DECLARE @mergepublishdb_bit int
declare @job_name nvarchar(100)
declare @alert_name nvarchar(100)
declare @dist_rpcname sysname
declare @alert_id int
--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END
SELECT @transpublishdb_bit = 1
SELECT @mergepublishdb_bit = 4
SELECT @foundSubscriber = 0
SELECT @distbit = 16
-- Get distributor name
select @distributor = datasource, @dist_rpcname = srvname from master.dbo.sysservers
WHERE srvstatus & 8 <> 0
if @distributor is null
BEGIN
RAISERROR (21043, 16, -1)
RETURN(1)
END
if @no_checks = 1
begin
-- We are in bruteforce cleanup mode, drop everything.
DECLARE hCdropdistributor CURSOR LOCAL FAST_FORWARD FOR
SELECT name, N'publish' FROM master.dbo.sysdatabases
WHERE (category & @transpublishdb_bit) <> 0
UNION
select name, N'merge publish' from master.dbo.sysdatabases
WHERE (category & @mergepublishdb_bit) <> 0
FOR READ ONLY
OPEN hCdropdistributor
FETCH hCdropdistributor INTO @name, @optname
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE @retcode = sys.sp_replicationdboption @dbname = @name,
@optname = @optname,
@value = 'false',
@ignore_distributor = @ignore_distributor
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
RETURN (1)
END
FETCH hCdropdistributor INTO @name, @optname
end
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
-- Drop subscribers of local SQL Server publisher
EXECUTE @retcode = sys.sp_dropsubscriber @subscriber = 'all',
@ignore_distributor = @ignore_distributor
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
-- Drop subscribers and associated subscriptions of heterogeneous publishers using this server
-- as their distributor
IF UPPER(@distributor) = UPPER(@@SERVERNAME)
begin
if exists (select * from msdb.sys.objects where name = 'MSdistpublishers'
and type = 'U')
begin
-- Clean up heterogeneous subscribers
DECLARE hCdrophsubscriber CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM msdb..MSdistpublishers
WHERE publisher_type <> 'MSSQLSERVER'
FOR READ ONLY
OPEN hCdrophsubscriber
FETCH hCdrophsubscriber INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
exec @retcode = sys.sp_dropsubscriber @subscriber = 'all',
@ignore_distributor = @ignore_distributor,
@publisher = @name,
@reserved = 'drop_subscriptions'
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
CLOSE hCdrophsubscriber
DEALLOCATE hCdrophsubscriber
RETURN (1)
END
FETCH hCdrophsubscriber INTO @name
end
CLOSE hCdrophsubscriber
DEALLOCATE hCdrophsubscriber
end
end
IF UPPER(@distributor) = UPPER(@@SERVERNAME)
begin
if exists (select * from msdb.sys.objects where name = 'MSdistpublishers'
and type = 'U')
begin
-- Clean up dist publishers
DECLARE hCdropdistributor CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM msdb..MSdistpublishers
FOR READ ONLY
OPEN hCdropdistributor
FETCH hCdropdistributor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
exec @retcode = sys.sp_dropdistpublisher @publisher = @name,
@no_checks = @no_checks,
@ignore_distributor = @ignore_distributor
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
RETURN (1)
END
FETCH hCdropdistributor INTO @name
end
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
end
if (@ignore_distributor = 0
or @no_checks = 1)
and exists (select * from msdb.sys.objects where name = 'MSdistributiondbs' and type = 'U')
and exists (select * from msdb.sys.objects where name = 'MSdistpublishers' and type = 'U')
begin
-- Clean up distribution dbs
DECLARE hCdropdistributor CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM msdb..MSdistributiondbs
FOR READ ONLY
OPEN hCdropdistributor
FETCH hCdropdistributor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
exec @retcode = sys.sp_dropdistributiondb @database = @name
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
RETURN (1)
END
FETCH hCdropdistributor INTO @name
end
CLOSE hCdropdistributor
DEALLOCATE hCdropdistributor
end
end
end
-- If everything should be cleaned up when we reach here with @no_checks = 1
--
-- If local distributor, check if there are any distributor databases
--
IF UPPER(@distributor) = UPPER(@@SERVERNAME)
BEGIN
if exists (select * from msdb.sys.objects where name = 'MSdistributiondbs'
and type = 'U')
begin
IF EXISTS (SELECT * FROM msdb..MSdistributiondbs)
BEGIN
RAISERROR (14121, 16, -1, @distributor)
RETURN(1)
END
end
END
ELSE
begin
-- Check to see if there are database published.
if exists (SELECT * FROM master.dbo.sysdatabases
WHERE (category & @transpublishdb_bit) <> 0 or
(category & @mergepublishdb_bit) <> 0)
begin
raiserror(21045, 16, -1)
return(1)
end
end
--
-- if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
--
if @ignore_distributor = 0
begin
--
-- Get distribution server information
--
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @@SERVERNAME,
@distribdb = @distribdb OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
IF @distribdb is NOT NULL
BEGIN
--
-- Deactivate the dist publisher at the distributor
-- Only do this if @distribdb is NOT NULL, which means the dist publisher
-- if defined.
--
SELECT @proc = RTRIM(@dist_rpcname) + '.master.sys.sp_changedistpublisher'
EXECUTE @retcode = @proc @@SERVERNAME, 'active','false'
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
END
end
--
-- Clear the server option to indicate that this is a distributor.
--
EXECUTE @retcode = sys.sp_serveroption @dist_rpcname, 'dist', false
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN(1)
END
-- Prevent dropping local server entry accidentally if user
-- set 'dist' server option on local server.
if UPPER(@dist_rpcname) <> UPPER(@@servername)
begin
-- get the servername that was persisted in the sysservers table
SELECT @dist_rpcname = sys.fn_getpersistedservernamecasevariation(@dist_rpcname) collate database_default
exec @retcode = sys.sp_dropserver @dist_rpcname, 'droplogins'
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN(1)
END
end
-- Drop table after unmark distributor to prevent
-- sp_helpdist* failures.
-- If local, Drop replication category and alerts --
IF UPPER(@distributor) = UPPER(@@SERVERNAME)
BEGIN
-- Attempts to drop distributor_login, no big deal if it's still in use and we can't drop, don't quit here.
declare @distributor_login sysname
select @distributor_login = 'distributor_admin'
if exists (select * from master.dbo.syslogins where loginname = @distributor_login collate database_default)
begin
EXEC @retcode = sys.sp_droplogin @loginame = @distributor_login
end
-- Drop Distributor Alerts and Jobs
exec @retcode = sys.sp_MSdrop_distributor_alerts_and_responses
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
return (1)
END
-- Drop the two system tables.
if exists (select * from msdb.sys.objects where name = 'MSdistpublishers'
and type = 'U')
drop table msdb..MSdistpublishers
if @@error <> 0
return 1 ;
if exists (select * from msdb.sys.objects where name = 'MSdistributiondbs'
and type = 'U')
drop table msdb..MSdistributiondbs
if @@error <> 0
return 1 ;
if exists (select * from msdb.sys.objects where name = 'MSdistributor'
and type = 'U')
drop table msdb..MSdistributor
if @@error <> 0
return 1 ;
if exists (select * from msdb.sys.objects where name = 'sysreplicationalerts'
and type = 'U')
drop table msdb.dbo.sysreplicationalerts
if @@error <> 0
return 1 ;
if exists (select * from msdb.sys.objects where name = 'MSagent_profiles'
and type = 'U')
drop table msdb..MSagent_profiles
if @@error <> 0
return 1 ;
if exists (select * from msdb.sys.objects where name = 'MSagent_parameters'
and type = 'U')
drop table msdb..MSagent_parameters
if @@error <> 0
return 1 ;
END
EXEC @retcode = sys.sp_MSrepl_drop_expired_sub_cleanup_job
-- Nnregister sp_MSrepl_startup as a startup stored procedure
exec @retcode = master.sys.sp_procoption 'sp_MSrepl_startup', 'startup', 'false'
if @@error <> 0 or @retcode <> 0
return 1
RETURN (0)
No comments:
Post a Comment