T-SQL to find if a Database is Subscribed on the Subscriber in Transactional Replication

sql-server sql-server-2008 transactional-replication
Afroz picture Afroz · Jun 5, 2013 · Viewed 8.2k times · Source

T-SQL to find if a Database is Subscribed on the Subscriber in Transactional Replication. I don't want to query Distribution for the details.

The following doesn't work.

SELECT is_subscribed FROM sys.databases

SELECT DATABASEPROPERTYEX('database', 'IsSubscribed')

Answer

Afroz picture Afroz · Jun 5, 2013

So far this is the best way I found but I am not sure if this works in Non-Transactional Replication.

SELECT COALESCE(OBJECTPROPERTY(OBJECT_ID('dbo.MSreplication_objects'), 'IsMSShipped'),0) AS IsSubscribed

OR

SELECT name
FROM sys.databases
WHERE OBJECT_ID(name+'.dbo.MSreplication_objects') IS NOT NULL

Related questions

SQL Server Replication - No replicated transactions are available

I have created a publisher (on sql server 2008 r2), create transaction replication and have created a new database on subscriber sql instance (SQL Server 2012 Express). After I have created a new subscription on SQL Server 2012 Express. But My new database …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More