Is this Database a Replication Subscriber?

In recent weeks I've been programming replication. One of the last UDFs that I had to implement is udf_Rep_IsReplica. This UDF enables my stored procedures to determine if the database in which they are executing is a subscriber (replica) or if it's the publisher.

I didn't find any built-in function that would give me this information so I based the result on the existence of the MSReplication_subscription table and there being at least one row in that table. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_Rep_IsReplica(

) RETURNS BIT -- 1 if this database is a replica, 0 if it is not

/*
* Returns true of this database is a replication subscriber 
* with at least one subscription.
*
* Example:
select dbo.udf_Rep_IsReplica () [Is Replica]
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #20 5/4/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/

AS BEGIN

   DECLARE @RowsInMSreplication_subscriptions int 
   DECLARE @Result BIT

   SET @Result = 0 -- Assume false

   if OBJECT_ID('MSreplication_subscriptions') IS NOT NULL BEGIN
     SELECT @RowsInMSreplication_subscriptions = COUNT(*)
           FROM MSreplication_subscriptions
     SET @Result = CASE 
                     WHEN @RowsInMSreplication_subscriptions > 0 
                     THEN 1 ELSE 0 END
   END

   RETURN @Result

END
GO

GRANT EXEC on dbo.udf_Rep_IsReplica TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Here's a test in a database that is not a subscriber:

select dbo.udf_Rep_IsReplica () [Is Replica]
GO

(Results)
Is Replica 
---------- 
         0 

Here's a test in a database that is a subscriber:

select dbo.udf_Rep_IsReplica () [Is Replica]
GO

(Results)
Is Replica 
---------- 
         1 

I use this UDF in stored procedure logic that has to behave differently depending on whether or not the database is a subscriber (Replica) or publisher. In my case when the database is the publisher, one of the stored procedures runs a query on a DB2 linked server to get the next incident number. But when the application is running with the replica database it's detached from the network and can't get to the DB2 database, which is on an IBM iSeries (f.k.a AS/400) computer. In that case it has to generate it's own temporary number.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick