배포자 및 게시자 정보 스크립트Distributor and Publisher Information Script

이 스크립트는 시스템 테이블 및 복제 저장 프로시저를 사용하여 배포자 및 게시자의 개체에 대한 일반적인 질문에 대해 응답할 수 있습니다.This script uses system tables and replication stored procedures to answer questions commonly asked about objects at the Distributor and Publisher. 스크립트는 "현재 상태로" 사용할 수 있으며 사용자 지정 스크립트의 기준도 제공할 수 있습니다.The script can be used "as-is" and can also provide the basis for customized scripts. 스크립트를 사용자 환경에서 실행하려면 다음 두 가지를 수정해야 합니다.The script might require two modifications to run in your environment:

  • 사용자의 게시 데이터베이스 이름을 사용할 수 있도록 use AdventureWorks2012 줄을 변경합니다.Change the line use AdventureWorks2012 to use the name of your publication database.

  • exec sp_helparticle @publication='<PublicationName>' 줄에서 주석(--)을 제거하고 <PublicationName>을 게시 이름으로 바꿉니다.Remove the comments (--) from the line exec sp_helparticle @publication='<PublicationName>' and replace <PublicationName> with the name of a publication.

--********** 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 AdventureWorks2012;  
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;  

관련 항목:See Also

복제 관리자를 위한 질문과 대답 Frequently Asked Questions for Replication Administrators
sp_get_distributor(Transact-SQL) sp_get_distributor (Transact-SQL)
sp_helparticle(Transact-SQL) sp_helparticle (Transact-SQL)
sp_helpdistributiondb(Transact-SQL) sp_helpdistributiondb (Transact-SQL)
sp_helpdistpublisher(Transact-SQL) sp_helpdistpublisher (Transact-SQL)
sp_helpdistributor(Transact-SQL) sp_helpdistributor (Transact-SQL)
sp_helpmergearticle(Transact-SQL) sp_helpmergearticle (Transact-SQL)
sp_helpmergepublication(Transact-SQL) sp_helpmergepublication (Transact-SQL)
sp_helppublication(Transact-SQL) sp_helppublication (Transact-SQL)
sp_helpreplicationdboption(Transact-SQL) sp_helpreplicationdboption (Transact-SQL)
sp_helpsubscriberinfo(Transact-SQL) sp_helpsubscriberinfo (Transact-SQL)
sys.columns(Transact-SQL) sys.columns (Transact-SQL)
sys.databases(Transact-SQL) sys.databases (Transact-SQL)
sys.procedures(Transact-SQL) sys.procedures (Transact-SQL)
sys.servers(Transact-SQL) sys.servers (Transact-SQL)
sys.tables(Transact-SQL) sys.tables (Transact-SQL)
sys.views(Transact-SQL) sys.views (Transact-SQL)