How to detect said database is primary Replica or Are You the Primary Replica? in SQL Server 2014 and SQL 2016

Are You the Primary Replica? in SQL Server 2014 and SQL 2016

-----------------------------------------------------------------------------------------------------------------------

How to identify whether or not a database on the current server is the primary or secondary replica.

The good news is on  SQL Server 2014 / SQL Server 2016  has included a DMO for same and the name is sys.fn_hadr_is_primary_replica 

Declare @Status int
SELECT  @Status=sys.fn_hadr_is_primary_replica ('AdventureWorks2014');
print @Status
if @Status = 1 
  begin
  print 'database on the current instance is the primary replica'
  end
  else
    begin
  if @Status =0
         begin
     print 'database on the current instance is not in primary replica'
  end
           else
             begin
                if @Status is NULL
                   begin
                     print 'Kindly provide correct database Name'
                   end

          end
 end

In SQL Server 2012 you can create this function and use for finding database is part of Primary Replica.
--------------------------------------------------------------------------------------------------------------------------------------------------------

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_is_database_primary_replica](@database_name sysname) returns bit as

begin

declare
@is_primary bit

declare
@is_role_primary tinyint 

select @is_role_primary =role
from sys.dm_hadr_availability_replica_states where replica_id=

(select replica_id from sys.dm_hadr_database_replica_states where
database_id=DB_ID(@database_name)  and is_local = 1 ); 

If @is_role_primary = 1               

set @is_primary = 1

else

set @is_primary = 0

return @is_primary

End 
------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Another script does not require to put a database Name */

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

  and o.id = object_id(N'tempdb..#AGdetails')
)
begin
DROP TABLE #AGdetails;
end;


declare @rd varchar(10)
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT

   AGC.name -- Availability Group
, RCS.replica_server_name -- SQL cluster node name
, ARS.role_desc  -- Replica Role
, AGL.dns_name  -- Listener Name

into #AGdetails
FROM
sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END

select @rd=role_desc from #AGdetails

if @rd = 'PRIMARY'

print  @@servername + ' is Primary Replica'
else
print @@servername + ' is Secondary Replica'
---------------------------------------------------------------------------------
output:-
(1 row(s) affected)

SQLNODE1\SQl1  is Primary Replica

-------------------------------------------------------------------------------------------------------------------
/* Check Which Availability group is primary on this replica - SQLNODE1\SQl1 .. */

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')
  and o.id = object_id(N'tempdb..#Replicadetails')
              )
begin
DROP TABLE #Replicadetails;
end;
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
into #Replicadetails
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

select 'The primary Availability group for this replica  ' + @@servername +   ' is '  +  Name from #Replicadetails where LocalReplicaRole = 1

Output

-----------------------------------------------

The primary Availability group for this SQLNODE1\SQl1   is AG1