The database principal owns a schema in the database, and cannot be dropped

Problem

You are trying to drop a database user, but are getting the following error message:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

This error is self-explanatory as it tells you that the database user you are trying to drop is the database schema owner.

Resolution

To successfully drop the database user, you must find all the schemas that are owned by the database user, and then transfer their ownership to another user.

Here is the Transact-SQL script, which I wrote a while ago, to drop the database user. This script first transfer’s ownership of all database schemas associated with particular database user to the specified database user, and then drops that database user from the database.

To use this script, change the following two local variables of this script:

  • @SQLUser – Specify the name of the database user that you want to drop
  • @NewSchemaOwner – Specify the name of the database user that will be used as new schema owner for the schemas that is owned by the database user, which you are dropping

-- Ensure a USE database_name statement has been executed first.
SET NOCOUNT ON;

DECLARE @ID [int] ,
		@CurrentCommand [nvarchar](MAX) ,
		@ErrorMessage   [nvarchar](2000) ,
		@SQLUser        [sysname] , --Specify the name of the database user that you want to drop
		@NewSchemaOwner [sysname];  --Specify the name of the database user that will be used as new schema
								    --owner for the schemas that is owned by the database user you are dropping

SET @SQLUser = N'Specify_Database_User_You_Want_To_Drop'; --Example: testuser
SET @NewSchemaOwner = N'Specify_Database_User_Who_Will_User_As_New_Schema_Owner'; --Example: liveuser

DECLARE @Work_To_Do TABLE
    (
      [ID] [int] IDENTITY(1, 1)
                 PRIMARY KEY ,
      [TSQL_Text] [varchar](1024) ,
      [Completed] [bit]
    );

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'ALTER AUTHORIZATION ON SCHEMA::' + [name] + SPACE(1) + 'TO'
                + SPACE(1) + QUOTENAME(@NewSchemaOwner) ,
                0
        FROM    [sys].[schemas]
        WHERE   [principal_id] = USER_ID(@SQLUser);

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'DROP USER' + SPACE(1) + @SQLUser ,
                0

SELECT  @ID = MIN([ID])
FROM    @Work_To_Do
WHERE   [Completed] = 0;

WHILE @ID IS NOT NULL
    BEGIN
        SELECT  @CurrentCommand = [TSQL_Text]
        FROM    @Work_To_Do
        WHERE   [ID] = @ID;

        BEGIN TRY
            EXEC [sys].[sp_executesql] @CurrentCommand
            PRINT @CurrentCommand
        END TRY
        BEGIN CATCH

            SET @ErrorMessage = N'"Oops, an error occurred that could not be resolved. For more information, see below:'
                + CHAR(13) + ERROR_MESSAGE() 

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

            GOTO ChooseNextCommand
        END CATCH

        ChooseNextCommand:

        UPDATE  @Work_To_Do
        SET     [Completed] = 1
        WHERE   [ID] = @ID

        SELECT  @ID = MIN([ID])
        FROM    @Work_To_Do
        WHERE   [Completed] = 0
    END;

SET NOCOUNT OFF;

Advertisements

Rebuild all indexes on all tables in the SQL Server database

One of the key tasks of a DBA is to maintain the database indexes and make sure they are not fragmented. You can use a sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes based on fragmentation. However, sometimes we may need to rebuild all indexes on all tables in the database, especially if you have to change any index property such as fill factor, compression, etc.

For this task, I wrote a following script that accepts parameters to change the properties of the indexes, and dynamically generate and execute ALTER INDEX statements. This script is compatible with SQL Server 2005 and above versions.

Here is this script:

--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments				Data Type				Description
--------------			------------			------------
--@FillFactor			[int]					Specifies a percentage that indicates how full the Database Engine should make the leaf level
--												of each index page during index creation or alteration. The valid inputs for this parameter
--												must be an integer value from 1 to 100 The default is 0.
--												For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.

--@PadIndex				[varchar](3)			Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
--												because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
--												for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
--												overrides the percentage to allow for the minimum. The number of rows on an intermediate
--												index page is never less than two, regardless of how low the value of fillfactor. The valid
--												inputs for this parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.

--@SortInTempDB			[varchar](3)			Specifies whether to store temporary sort results in tempdb. The valid inputs for this
--												parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.

--@OnlineRebuild		[varchar](3)			Specifies whether underlying tables and associated indexes are available for queries and data
--												modification during the index operation. The valid inputs for this parameter are ON or OFF.
--												The default is OFF.
--												Note: Online index operations are only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.

--@DataCompression		[varchar](4)			Specifies the data compression option for the specified index, partition number, or range of
--												partitions. The options  for this parameter are as follows:
--													> NONE - Index or specified partitions are not compressed.
--													> ROW  - Index or specified partitions are compressed by using row compression.
--													> PAGE - Index or specified partitions are compressed by using page compression.
--												The default is NONE.
--												Note: Data compression feature is only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.

--@MaxDOP				[int]					Overrides the max degree of parallelism configuration option for the duration of the index
--												operation. The valid input for this parameter can be between 0 and 64, but should not exceed
--												number of processors available to SQL Server.
--												For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE  @Version							[numeric] (18, 10)
		,@SQLStatementID					[int]
		,@CurrentTSQLToExecute				[nvarchar](max)
		,@FillFactor						[int]		 = 100 -- Change if needed
		,@PadIndex							[varchar](3) = N'OFF' -- Change if needed
		,@SortInTempDB						[varchar](3) = N'OFF' -- Change if needed
		,@OnlineRebuild						[varchar](3) = N'OFF' -- Change if needed
		,@LOBCompaction						[varchar](3) = N'ON' -- Change if needed
		,@DataCompression					[varchar](4) = N'NONE' -- Change if needed
		,@MaxDOP							[int]		 = NULL -- Change if needed
		,@IncludeDataCompressionArgument	[char](1);

IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
    DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
    (
      [sql_id] [int] IDENTITY(1, 1)
                     PRIMARY KEY ,
      [tsql_text] [varchar](1024) ,
      [completed] [bit]
    )

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))

IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
	AND (
		@Version >= 10.0
		AND SERVERPROPERTY(N'EngineEdition') = 3
		)
BEGIN
	SET @IncludeDataCompressionArgument = N'Y'
END

IF @IncludeDataCompressionArgument IS NULL
BEGIN
	SET @IncludeDataCompressionArgument = N'N'
END

INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
		WHEN @PadIndex IS NULL
			THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
					WHEN 1
						THEN 'ON'
					WHEN 0
						THEN 'OFF'
					END
		ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
		END + CASE
		WHEN @FillFactor IS NULL
			THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
		ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
		END + CASE
		WHEN @SortInTempDB IS NULL
			THEN ''
		ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
		END + CASE
		WHEN @OnlineRebuild IS NULL
			THEN ''
		ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
		END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + CASE
		WHEN @IncludeDataCompressionArgument = N'Y'
			THEN CASE
					WHEN @DataCompression IS NULL
						THEN ''
					ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
					END
		ELSE ''
		END + CASE
		WHEN @MaxDop IS NULL
			THEN ''
		ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
		END + SPACE(1) + ')'
	,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
	AND i.[index_id] > 0
	AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
	AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
	AND st.[name] = i.[name]

SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0

WHILE @SQLStatementID IS NOT NULL
BEGIN
	SELECT @CurrentTSQLToExecute = [tsql_text]
	FROM #Work_To_Do
	WHERE [sql_id] = @SQLStatementID

	PRINT @CurrentTSQLToExecute

	EXEC [sys].[sp_executesql] @CurrentTSQLToExecute

	UPDATE #Work_To_Do
	SET [completed] = 1
	WHERE [sql_id] = @SQLStatementID

	SELECT @SQLStatementID = MIN([sql_id])
	FROM #Work_To_Do
	WHERE [completed] = 0
END

Removing part of string before and after specific character using Transact-SQL string functions

Problem

Today, one of the developers come to me and asked me the question that is there any T-SQL function that he could use to remove everything before and after a specific character in string. For example, if the table contains the full names of the people in the format as firstname comma surname (Farooq,Basit). He would like to retrieve the first and surnames of people in separate columns of result set.

Solution

The easiest solution that comes to my mind for this problem is to use LEFT and REPLACE string function combined CHARINDEX and LEN string function.

To remove the part of string after the specific character, you use these transact-sql string functions as follow:

SELECT LEFT(string_expression, CHARINDEX(expression_to_find, string_expression) - 1)

To remove the part of string before the specific character, you use these transact-sql string functions as follow:

SELECT REPLACE(SUBSTRING(string_expression, CHARINDEX(expression_to_find, string_expression), LEN(string_expression)), string_pattern, string_replacement)

Demo

For example, I created the following table that contains the sample dummy data. See screen shot below that shows the format of data in this sample table:

tsql_split01

Below is the query that splits the data base on comma(,) in FullName column to FirstName and Surname :

SELECT   [FullName]
	    ,LEFT([FullName], CHARINDEX(',', [FullName]) - 1) AS [Surname]
	    ,REPLACE(SUBSTRING([FullName], CHARINDEX(',', [FullName]), LEN([FullName])), ',', '') AS [FirstName]
FROM    Employee

Example Output

tsql_split02

Hope you will like this post…:)

Transact-SQL query to obtain basic information about partitioned tables

Here is the simple query that returns basic information about all tables in a database that are partitioned:

SELECT SCHEMA_NAME([schema_id]) AS [schema_name]
      ,t.[name] AS [table_name]
      ,i.[name] AS [index_name]
      ,i.[type_desc] AS [index_type]
      ,ps.[name] AS [partition_scheme]
      ,pf.[name] AS [partition_function]
      ,p.[partition_number]
      ,r.[value] AS [current_partition_range_boundary_value]
      ,p.[rows] AS [partition_rows]
      ,p.[data_compression_desc]
FROM sys.tables t
INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id]
                           AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]
INNER JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r ON pf.[function_id] = r.[function_id]
    AND r.[boundary_id] = p.[partition_number]
GROUP BY SCHEMA_NAME([schema_id])
        ,t.[name]
        ,i.[name]
        ,i.[type_desc]
        ,ps.[name]
        ,pf.[name]
        ,p.[partition_number]
        ,r.[value]
        ,p.[rows]
        ,p.[data_compression_desc]
ORDER BY SCHEMA_NAME([schema_id])
        ,t.[name]
        ,i.[name]
        ,p.[partition_number];

The query returns the following columns:

  • schema_name – name of the partitioned table schema.
  • table_name – name of the partitioned table.
  • index_name – name of the partitioned index.
  • index_type – type of the partitioned index.
  • partition_scheme – name of the partition scheme.
  • partition_function – name of the partition function.
  • partition_number – indicate the partition number.
  • current_partition_range_boundary_value – partition actual boundary value.
  • partition_rows – indicates approximate number of rows in current partition.
  • data_compression_desc – Indicates the state of compression for each partition.

This example query uses following system views: sys.tables, sys.partitions, sys.indexes, sys.partition_schemes, sys.partition_functions and sys.partition_range_values.

For more information on table partitioning, see “Partitioned Tables and Indexes” on MSDN website.

Transact-SQL (T-SQL) query to return the status and detail information for all SQL Server Full-Text Catalogs on an SQL Server instance

We have about 200 user databases in which we have the full-text search enabled and these databases contain several tables. As part of my daily checks, I have to check the status of all full-text catalogs, to ensure that all full-text catalogs are successfully populated without errors. However, to check the status of every full-text catalog using SQL Server Management Studio is a challenge and very time consuming task.  So, to make my life easier, I wrote the following T-SQL script that quickly returns the status of all full-text catalogs and their population status.

SELECT DB_NAME(ftsac.[database_id]) AS [db_name]
	,DATABASEPROPERTYEX(DB_NAME(ftsac.[database_id]), 'IsFulltextEnabled') AS [is_ft_enabled]
	,ftsac.[name] AS [catalog_name]
	,mfs.[name] AS [ft_catalog_file_logical_name]
	,mfs.[physical_name] AS [ft_catalog_file_physical_name]
	,OBJECT_NAME(ftsip.[table_id]) AS [table_name]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'IndexSize') AS [ft_catalog_logical_index_size_in_mb]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'AccentSensitivity') AS [is_accent_sensitive]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'UniqueKeyCount') AS [unique_key_count]
	,ftsac.[row_count_in_thousands]
	,ftsip.[is_clustered_index_scan]
	,ftsip.[range_count]
	,FULLTEXTCATALOGPROPERTY(ftsac.[name], 'ImportStatus') AS [import_status]
	,ftsac.[status_description] AS [current_state_of_fts_catalog]
	,ftsac.[is_paused]
	,(
		SELECT CASE FULLTEXTCATALOGPROPERTY(ftsac.[name], 'PopulateStatus')
				WHEN 0
					THEN 'Idle'
				WHEN 1
					THEN 'Full Population In Progress'
				WHEN 2
					THEN 'Paused'
				WHEN 3
					THEN 'Throttled'
				WHEN 4
					THEN 'Recovering'
				WHEN 5
					THEN 'Shutdown'
				WHEN 6
					THEN 'Incremental Population In Progress'
				WHEN 7
					THEN 'Building Index'
				WHEN 8
					THEN 'Disk Full. Paused'
				WHEN 9
					THEN 'Change Tracking'
				END
		) AS [population_status]
	,ftsip.[population_type_description] AS [ft_catalog_population_type]
	,ftsip.[status_description] AS [status_of_population]
	,ftsip.[completion_type_description]
	,ftsip.[queued_population_type_description]
	,ftsip.[start_time]
	,DATEADD(ss, FULLTEXTCATALOGPROPERTY(ftsac.[name], 'PopulateCompletionAge'), '1/1/1990') AS [last_populated]
FROM [sys].[dm_fts_active_catalogs] ftsac
INNER JOIN [sys].[databases] dbs
	ON dbs.[database_id] = ftsac.[database_id]
LEFT JOIN [sys].[master_files] mfs
	ON mfs.[database_id] = dbs.[database_id]
		AND mfs.[physical_name] NOT LIKE '%.mdf'
		AND mfs.[physical_name] NOT LIKE '%.ndf'
		AND mfs.[physical_name] NOT LIKE '%.ldf'
CROSS JOIN [sys].[dm_fts_index_population] ftsip
WHERE ftsac.[database_id] = ftsip.[database_id]
	AND ftsac.[catalog_id] = ftsip.[catalog_id];

Here is the description of the columns of this script result set:

  • db_name – Name of the SQL Server database, unique within an instance of SQL Server
  • is_ft_enabled – The value of 1 indicates that the full-text and semantic indexing is enabled
  • ft_catalog_file_logical_name – Returns the logical file name of the full-text index catalog file
  • ft_catalog_file_physical_name – Returns the phyisical file name of the full-text index catalog file
  • table_name – Returns the name of the table where full-text index exists
  • ft_catalog_logical_index_size_in_mb – Returns the logical size of the full-text catalog in megabytes(MB)
  • is_accent_sensitive – Returns the accent-sensitivity setting for full-text catalog. The value of 1 indicates that full-text catalog is accent sensitive
  • unique_key_count – Returns the number of unique keys in the full-text catalog
  • row_count_in_thousands – Returns the estimated number of rows (in thousands) in all full-text indexes in this full-text catalog
  • is_clustered_index_scan – Indicates whether the population involves a scan on the clustered index
  • range_count – Returns the number of sub-ranges into which this population has been parallelized
  • import_status – Indicates whether the full-text catalog is being imported. The value of 1 indicates that the full-text catalog is being imported
  • current_state_of_fts_catalog – Returns the state of the full-text catalog
  • is_paused – Indicates whether the population of the active full-text catalog has been paused
  • population_status – Returns the status of current population
  • ft_catalog_population_type – Returns the type of full-text catalog population type
  • status_of_population – Returns the status of this population
  • completion_type_description – Returns the description of status of the population
  • queued_population_type_description – Returns description of the population to follow, if any. For example, when CHANGE TRACKING = AUTO and the initial full population is in progress, this column would show “Auto population.”
  • start_time – Returns the time that the population started.
  • last_populated – Returns the time when the last full-text index population completed

I wrote this script using function FULLTEXTCATALOGPROPERTY and following system views and dmvs:

Hope you will find this post useful 😉 .

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) script

Today, I received email from one of my blog follower asking if there is any DMV or SQL script, which he can use to find the following information about the processors that are available to and consumed by SQL Server:

  • Total number of physical CPUs
  • Total number of physical cores per CPUs
  • Total number of physical cores
  • Total number of virtual CPUs
  • Processor type (x86 or x64)

As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

DECLARE @xp_msver TABLE (
	[idx] [int] NULL
	,[c_name] [varchar](100) NULL
	,[int_val] [float] NULL
	,[c_val] [varchar](128) NULL
	)

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
	SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [number_of_cores_per_cpu]
		,CASE 
			WHEN hyperthread_ratio = cpu_count
				THEN cpu_count
			ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
			END AS [total_number_of_cores]
		,[cpu_count] AS [number_of_virtual_cpus]
		,(
			SELECT [c_val]
			FROM @xp_msver
			WHERE [c_name] = 'Platform'
			) AS [cpu_category]
	FROM [sys].[dm_os_sys_info]
	)
SELECT [number_of_physical_cpus]
	,[number_of_cores_per_cpu]
	,[total_number_of_cores]
	,[number_of_virtual_cpus]
	,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]

The code of this script is tested on SQL Server 2005 and above versions.

Hope you will this post useful 😉 .

Monitor availability groups and availability replicas status information using T-SQL

Just a quick blog post to share a query, which I wrote to monitor availability groups and replicas and the associated databases:

WITH [AvailabilityGroupReplicaCTE]
AS (
	SELECT dc.[database_name]
		,dr.[synchronization_state_desc]
		,dr.[suspend_reason_desc]
		,dr.[synchronization_health_desc]
		,dr.[replica_id]
		,ar.[availability_mode_desc]
		,ar.[primary_role_allow_connections_desc]
		,ar.[secondary_role_allow_connections_desc]
		,ar.[failover_mode_desc]
		,ar.[endpoint_url]
		,ar.[owner_sid]
		,ar.[create_date]
		,ar.[modify_date]
		,dr.[recovery_lsn]
		,dr.[truncation_lsn]
		,dr.[last_sent_lsn]
		,dr.[last_sent_time]
		,dr.[last_received_lsn]
		,dr.[last_received_time]
		,dr.[last_hardened_lsn]
		,dr.[last_hardened_time]
		,dr.[last_redone_lsn]
		,dr.[last_redone_time]
		,dr.[redo_queue_size]
		,dr.[log_send_queue_size]
	FROM [sys].[dm_hadr_database_replica_states] dr
	INNER JOIN [sys].[availability_databases_cluster] dc
		ON dr.[group_database_id] = dc.[group_database_id]
	INNER JOIN [sys].[availability_replicas] ar
		ON ar.[replica_id] = dr.[replica_id]
	WHERE dr.[is_local] = 1
	)
	,[AvailabilityGroupReplicaDatabaseState] (
	[ReplicaID]
	,[ReplicaDBName]
	,[ReplicaServerName]
	,[JoinState]
	,[Role]
	,[AvailabilityMode]
	,[SynchronizationState]
	,[SynchronizationHealth]
	,[OperationalState]
	,[ConnectedState]
	,[SuspendReason]
	,[RecoveryHealth]
	,[RecoveryLSN]
	,[TruncationLSN]
	,[LastSentLSN]
	,[LastSentTime]
	,[LastReceivedLSN]
	,[LastReceivedTime]
	,[LastHardenedLSN]
	,[LastHardenedTime]
	,[LastRedoneLSN]
	,[LastRedoneTime]
	,[RedoQueueSize]
	,[LogSendQueueSize]
	,[PrimaryRoleAllowConnections]
	,[SecondaryRoleAllowConnections]
	,[FailoverMode]
	,[EndPointURL]
	,[Owner]
	,[CreateDate]
	,[ModifyDate]
	)
AS (
	SELECT c.[replica_id]
		,c.[database_name]
		,cs.[replica_server_name]
		,cs.[join_state_desc]
		,rs.[role_desc]
		,c.[availability_mode_desc]
		,c.[synchronization_state_desc]
		,c.[synchronization_health_desc]
		,rs.[operational_state_desc]
		,rs.[connected_state_desc]
		,c.[suspend_reason_desc]
		,rs.[recovery_health_desc]
		,c.[recovery_lsn]
		,c.[truncation_lsn]
		,c.[last_sent_lsn]
		,c.[last_sent_time]
		,c.[last_received_lsn]
		,c.[last_received_time]
		,c.[last_hardened_lsn]
		,c.[last_hardened_time]
		,c.[last_redone_lsn]
		,c.[last_redone_time]
		,c.[redo_queue_size]
		,c.[log_send_queue_size]
		,c.[primary_role_allow_connections_desc]
		,c.[secondary_role_allow_connections_desc]
		,c.[failover_mode_desc]
		,c.[endpoint_url]
		,sl.[name]
		,c.[create_date]
		,c.[modify_date]
	FROM [AvailabilityGroupReplicaCTE] c
	INNER JOIN [sys].[dm_hadr_availability_replica_states] rs
		ON rs.[replica_id] = c.[replica_id]
	INNER JOIN [sys].[dm_hadr_availability_replica_cluster_states] cs
		ON cs.[replica_id] = c.[replica_id]
	INNER JOIN [sys].[syslogins] sl
		ON c.[owner_sid] = sl.[sid]
	)
SELECT *
FROM [AvailabilityGroupReplicaDatabaseState];