How to find Publisher , Publisher server and Publisher databases in SQL Server Replication

--********** Execute at the Publisher in the master database **********--

1. Which databases are published for replication and what type of replication?
EXEC sp_helpreplicationdboption;

2. Which databases are published using snapshot replication or transnational replication?
SELECT name as tran_published_db FROM sys.databases WHERE is_published = 1;
3. Which databases are published using merge replication?
SELECT name as merge_published_db FROM sys.databases WHERE is_merge_published = 1;

4. What are the properties for Subscribers that subscribe to publications at this Publisher?
EXEC sp_helpsubscriberinfo;
--********** Execute at the Publisher in the publication database **********--

5. What are the snapshot and transactional publications in this database? 
EXEC sp_helppublication;
6. What are the articles in snapshot and transactional publications in this database?
--EXEC sp_helparticle @publication='name of a publication';
7. What are the merge publications in this database? 
EXEC sp_helpmergepublication;
8. What are the articles in merge publications in this database?
EXEC sp_helpmergearticle; -- to return information on articles for a single publication, specify @publication=''

9. 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;

10. 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;

11. 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;

No comments:

Post a Comment