How to check if your databases are part of a replication

If you need to check if there is any database on your SQL instance, related to replication, you have couple of different options – from manually checking through SSMS to using different options with T-SQL. Now I will share a simple script that you can use if you need to quickly check if your databases are part of a replication.

The script itself is exposing information in sys.databases, so you will need proper permissions to do that (I guess you are a sysadmin already :) )

SELECT 
	name as [Database name],
	CASE is_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Published],
	CASE is_merge_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Merge Published],
	CASE is_distributor 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Distributor],
	CASE is_subscribed 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

Once you have the output you will have to dig up a little bit to see the details about the replication your database(s) is part of. The script is just pointing you the direction. Here is an output:

replication_script

 

Note that you have two different columns for the publishing part – one for merge and one for transactional/snapshot replication.

If you want to write your own version of the script, you can also use DATABASEPROPERTYEX function – http://technet.microsoft.com/en-us/library/ms186823.aspx

One Reply to “How to check if your databases are part of a replication”

  1. easier

    SELECT COUNT(*) AS IsPublished
    FROM sys.databases
    WHERE (is_published | is_subscribed | is_merge_published) AND (name = @dbname)

    This will get you a list of the dbs that are published. you can optionally included distribution dbs by adding the is_distributor

    SELECT name
    FROM sys.databases
    WHERE (is_published | is_subscribed | is_merge_published | is_distributor = 1)

    To me anything worth doing is worth doing again so I create a user defined function (you could use a stored procedure just as easily):

    — ================================================
    — Author: J. Duke Rogers
    — Communicore Technologies &
    — Triangle Forensics
    — Create date: 07.25.20
    — Description: If @dbname is null or ” it uses
    — the database in which it is defined
    — WARNING: May cause black hole(s)
    — ================================================
    CREATE FUNCTION fn_IsReplicationEnabled
    (
    @dbname sysname =NULL
    )
    RETURNS bit
    AS
    BEGIN
    DECLARE @Result int

    IF (@dbname is null or @dbname=”)
    set @dbname=DB_NAME()

    SELECT @Result=COUNT(*)
    FROM
    sys.databases
    WHERE
    is_published | is_subscribed | is_merge_published=1 AND [name] = @dbname

    IF @Result is null or @Result 1
    return CAST(‘Something impossible has occurred, singularity and/or black hole
    imminent, don helmet!’ as bit)

    RETURN IIF(@Result=1,1,0)

    END
    GO

    IMHO

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.