Replication FAQ - how to know Distributor and Publisher with scripts
A newbie set of question that how to know the whether the current server is distributor or publisher?
Any pre-defined script to know any other publishers that are using as Distributor?
Well, there are such pre-defined scripts and stored procedures are availble within the SQL Server documentation such as BOL.
For instance, on a server side you can execute the following to get distributor, distribution database and any other publishers using this server as a Distributor :
use master
go
exec sp_get_distributor
select is_distributor from sys.servers where name='repl_distributor' and data_source=@@servername
--For the distribution databases properties execute:
exec sp_helpdistributor
exec sp_helpdistributiondb
exec sp_helpdistpublisher
Similarly you can also execute following set of scripts on the publisher side of replication such as know the type of replication, what databases are published within snapshot or transactional type of replication and their properties:
exec sp_helpreplicationdboption
select name as tran_published_db from sys.databases where is_published = 1
select name as merge_published_db from sys.databases where is_merge_published = 1
exec sp_helpsubscriberinfo
Further on the database level you could run the following script to see which objects 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