-- just a select on any of the replication flags
SELECT
name [Database]
FROM
sys.databases
WHERE
databases.NAME = DB_NAME()
AND
databases.is_published
= 1
OR
databases.is_merge_published
= 1
OR
databases.is_subscribed
= 1
OR databases.is_distributor = 1
You will see this output if a distribution database exists:
This next one is what I use to return the status of all user databases on an instance, indicating whether any are used in SQL Server Replication.
/* check if any db is used in replication */
SELECT
name [Database],
CASE
is_published WHEN 0 THEN
'No' ELSE 'Yes' END
[IsPublished],
CASE
is_merge_published WHEN 0 THEN 'No' ELSE 'Yes' END [IsMergePublished],
CASE
is_distributor WHEN 0 THEN
'No' ELSE 'Yes' END
[IsDistributor],
CASE
is_subscribed WHEN 0 THEN
'No' ELSE 'Yes' END
[IsSubscribed]
FROM
sys.databases
WHERE
database_id > 4
Here we see the cpriogbp is being published, and the distributor database is shown as well.
That's it! See these for much ore detail regarding Microsoft SQL Server Replication: