finding the Database name and publication name for replication jobs

Hi Friends,

one of my friends asked me a question where he wants to identify the publications related to a database from msdb jobs .

As this could be useful I thought of sharing the same.  Before going to details one has to know that how the format of the jobs would be.

We have 2 different kind  of subscriptions one is push and the other is pull and the format will be

push: <Publisher>-<Publication Database>-<Publication>-<Subscriber>-<number>

pull: <Publisher>-<Publication Database>-<Publication>-<Subscriber>-<Subscription Database>-<GUID>

By looking at this we need to split the strings by using '-' as separator. For that we need to make use of the below script

CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(

 @TEXT      varchar(8000)

,@COLUMN    tinyint

,@SEPARATOR char(1)

)RETURNS varchar(8000)

AS

  BEGIN

       DECLARE @POS_START  int = 1

       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

 

       WHILE (@COLUMN >1 AND @POS_END> 0)

         BEGIN

             SET @POS_START = @POS_END + 1

             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

             SET @COLUMN = @COLUMN - 1

         END

 

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1

       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

 

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)

  END

GO




Once done we can make use of it directly 

SELECT

  dbo.UFN_SEPARATES_COLUMNS(name, 1, '-') AS publihser_server_name,

  dbo.UFN_SEPARATES_COLUMNS(name, 2, '-') AS publication_database,

  dbo.UFN_SEPARATES_COLUMNS(name, 3, '-') AS publication_name,

  dbo.UFN_SEPARATES_COLUMNS(name, 4, '-') AS subscriber_server_name

from msdb..sysjobs where category_id=10 and dbo.UFN_SEPARATES_COLUMNS(name, 2, '-')='DBNAME'

This might be useful for those who have multiple publications for one database and need to differentiate them based on jobs.