Script de informações do Distribuidor e Publicador
Aplica-se a: SQL Server
Instância Gerenciada de SQL do Azure
Esse script usa tabelas de sistema e replicação de procedimentos armazenados para responder as perguntas frequentes sobre objetos no Distribuidor e no Publicador. O script pode ser usado assim como é, e pode fornecer também a base para scripts personalizados. O script poderá exigir duas modificações para ser executado em seu ambiente:
Altere a linha
use AdventureWorks2022
para usar o nome de seu banco de dados de publicação.Remova os comentários (
--
) da linhaexec sp_helparticle @publication='<PublicationName>'
e substitua <PublicationName> pelo nome de uma publicação.
--********** Execute at the Distributor in the master database **********--
USE master;
go
--Is the current server a Distributor?
--Is the distribution database installed?
--Are there other Publishers using this Distributor?
EXEC sp_get_distributor
--Is the current server a Distributor?
SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;
--Which databases on the Distributor are distribution databases?
SELECT name FROM sys.databases WHERE is_distributor = 1
--What are the Distributor and distribution database properties?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;
--********** Execute at the Publisher in the master database **********--
--Which databases are published for replication and what type of replication?
EXEC sp_helpreplicationdboption;
--Which databases are published using snapshot replication or transactional replication?
SELECT name as tran_published_db FROM sys.databases WHERE is_published = 1;
--Which databases are published using merge replication?
SELECT name as merge_published_db FROM sys.databases WHERE is_merge_published = 1;
--What are the properties for Subscribers that subscribe to publications at this Publisher?
EXEC sp_helpsubscriberinfo;
--********** Execute at the Publisher in the publication database **********--
USE AdventureWorks2022;
go
--What are the snapshot and transactional publications in this database?
EXEC sp_helppublication;
--What are the articles in snapshot and transactional publications in this database?
--REMOVE COMMENTS FROM NEXT LINE AND REPLACE <PublicationName> with the name of a publication
--EXEC sp_helparticle @publication='<PublicationName>';
--What are the merge publications in this database?
EXEC sp_helpmergepublication;
--What are the articles in merge publications in this database?
EXEC sp_helpmergearticle; -- to return information on articles for a single publication, specify @publication='<PublicationName>'
--Which objects in the database are published?
SELECT name AS published_object, schema_id, is_published AS is_tran_published, is_merge_published, is_schema_published
FROM sys.tables WHERE is_published = 1 or is_merge_published = 1 or is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.procedures WHERE is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.views WHERE is_schema_published = 1;
--Which columns are published in snapshot or transactional publications in this database?
SELECT object_name(object_id) AS tran_published_table, name AS published_column FROM sys.columns WHERE is_replicated = 1;
--Which columns are published in merge publications in this database?
SELECT object_name(object_id) AS merge_published_table, name AS published_column FROM sys.columns WHERE is_merge_published = 1;
Confira também
Perguntas frequentes para os administradores de replicação
sp_get_distributor (Transact-SQL)
sp_helparticle (Transact-SQL)
sp_helpdistributiondb (Transact-SQL)
sp_helpdistpublisher (Transact-SQL)
sp_helpdistributor (Transact-SQL)
sp_helpmergearticle (Transact-SQL)
sp_helpmergepublication (Transact-SQL)
sp_helppublication (Transact-SQL)
sp_helpreplicationdboption (Transact-SQL)
sp_helpsubscriberinfo (Transact-SQL)
sys.columns (Transact-SQL)
sys.databases (Transact-SQL)
sys.procedures (Transact-SQL)
sys.servers (Transact-SQL)
sys.tables (Transact-SQL)
sys.views (Transact-SQL)