DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

DECLARE @colSeperator NVARCHAR(20) 
SET			@colSeperator = ':,:'

PRINT @DescTag + ' Check for duplicate NetApp arrays and array interfaces'

DECLARE @errCode INT = 0
-- Parsing the array alias metadata
DECLARE @arrayId            INT = 0
DECLARE @arrayAlias			NVARCHAR(4000) = N''
DECLARE @metaDataId			INT = 0
DECLARE @metaDataType		INT = 0
DECLARE @refType			INT = 0
DECLARE @tblAlias           TABLE(arrayId INT, alias NVARCHAR(1024))
DECLARE @tblMetaData        TABLE	(arrayId INT, 
									metaDataId INT,
									metaDataType INT,
									RefType INT,
									metaData NVARCHAR(4000))
DECLARE @tblDupAliases		TABLE	(ctrlHostId_1 INT, 
									arrayId_1 NVARCHAR(1024),
									alias_1 NVARCHAR(4000),
									ctrlHostId_2 INT, 
									arrayId_2 NVARCHAR(1024),
									alias_2 NVARCHAR(4000))

DECLARE @arrayAliasDelim    NVARCHAR(2) = N'|'
DECLARE @SM_MDT_ARRAYALIAS  INT = 32
DECLARE @SM_MRT_ARRAY       INT = 5
DECLARE @openCurFlag		INT = 0

INSERT INTO @tblMetaData 
SELECT      RefId, MetaDataId, MetaDataType, RefType, CONVERT(NVARCHAR(4000), MetaData)
FROM        SMMetaData (NOLOCK) WHERE MetaDataType = @SM_MDT_ARRAYALIAS

DECLARE db_cursor CURSOR FOR  
SELECT	arrayId, MetaDataId, MetaDataType, RefType, metaData
FROM	@tblMetaData

SET	@errCode	= @@ERROR 
IF	@errCode != 0 GOTO CX_EXIT 

SET @openCurFlag = 1
OPEN db_cursor   

SET	@errCode	= @@ERROR 
IF	@errCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM db_cursor 
INTO @arrayId, @metaDataId, @metaDataType, @refType, @arrayAlias 
SET	@errCode	= @@ERROR 
IF	@errCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0   
BEGIN   
    INSERT INTO @tblAlias
    SELECT DISTINCT @arrayId, VALUE 
    FROM cte_split_inline(@arrayAlias, @arrayAliasDelim)
    
    FETCH NEXT 
    FROM db_cursor 
    INTO @arrayId, @metaDataId, @metaDataType, @refType, @arrayAlias 
    
    SET	@errCode	= @@ERROR 
	IF	@errCode != 0 GOTO CX_EXIT 

END   
CLOSE db_cursor   
DEALLOCATE db_cursor
SET @openCurFlag = 0

DECLARE @ctrlHostId_1	INT
DECLARE @ctrlHostId_2	INT
DECLARE @arrayId_1		NVARCHAR(1024)
DECLARE @arrayId_2		NVARCHAR(1024)
DECLARE @alias_1		NVARCHAR(4000)
DECLARE @alias_2		NVARCHAR(4000)

INSERT	INTO @tblDupAliases
SELECT	DISTINCT AHOST.ControlHostId, AHOST.SMArrayId, N'',
		BHOST.ControlHostId, BHOST.SMArrayId, BMDT.metaData
FROM	SMControlHost AHOST 
INNER JOIN @tblAlias B ON (AHOST.SMArrayId = B.alias AND AHOST.ControlHostId <> B.arrayId )
INNER JOIN SMControlHost BHOST ON B.arrayId = BHOST.ControlHostId
INNER JOIN @tblMetaData BMDT ON BHOST.ControlHostId = BMDT.arrayId

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

DECLARE db_cursor CURSOR FOR  
SELECT ctrlHostId_1, arrayId_1, alias_1, ctrlHostId_2, arrayId_2, alias_2
FROM @tblDupAliases

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

SET @openCurFlag = 1
OPEN db_cursor   

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM db_cursor 
INTO @ctrlHostId_1, @arrayId_1, @alias_1, @ctrlHostId_2, @arrayId_2, @alias_2

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0
BEGIN   
	print 'ControlHostId[' + CONVERT(NVARCHAR, @ctrlHostId_1) + '] SMArrayId[' + @arrayId_1 
		+ '] is an alias of ControlHostId[' 
		+ CONVERT(NVARCHAR, @ctrlHostId_2) + '] SMArrayId[' + @arrayId_2 
		+ '] with aliases[' + @alias_2 + ']'
    
    FETCH NEXT 
    FROM db_cursor 
    INTO @ctrlHostId_1, @arrayId_1, @alias_1, @ctrlHostId_2, @arrayId_2, @alias_2
    
    SET	@errCode	= @@ERROR
	IF	@errCode != 0 GOTO CX_EXIT 

END   
CLOSE db_cursor
DEALLOCATE db_cursor
SET @openCurFlag = 0

DELETE @tblDupAliases

INSERT INTO @tblDupAliases
SELECT  DISTINCT AHOST.ControlHostId, AHOST.SMArrayId, AMDT.metaData, 
		BHOST.ControlHostId, BHOST.SMArrayId, BMDT.metaData
FROM	@tblAlias A
INNER JOIN @tblMetaData AMDT ON A.arrayId = AMDT.arrayId
INNER JOIN SMControlHost AHOST ON A.arrayId = AHOST.ControlHostId
INNER JOIN @tblAlias B ON A.alias = B.alias AND A.arrayId < B.arrayId
INNER JOIN @tblMetaData BMDT ON B.arrayId = BMDT.arrayId
INNER JOIN SMControlHost BHOST ON B.arrayId = BHOST.ControlHostId

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

DECLARE db_cursor CURSOR FOR  
SELECT ctrlHostId_1, arrayId_1, alias_1, ctrlHostId_2, arrayId_2, alias_2
FROM @tblDupAliases

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

SET @openCurFlag = 1
OPEN db_cursor   

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM db_cursor 
INTO @ctrlHostId_1, @arrayId_1, @alias_1, @ctrlHostId_2, @arrayId_2, @alias_2

SET	@errCode	= @@ERROR
IF	@errCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0   
BEGIN   

	print 'ControlHostId[' + CONVERT(NVARCHAR, @ctrlHostId_1) + '] SMArrayId[' + @arrayId_1 
		+ '] with aliases[' + @alias_1 + '] has the same alias(es) as ControlHostId[' 
		+ CONVERT(NVARCHAR, @ctrlHostId_2) + '] SMArrayId[' + @arrayId_2 
		+ '] with aliases[' + @alias_2 + ']'
    
    FETCH NEXT 
    FROM db_cursor 
    INTO @ctrlHostId_1, @arrayId_1, @alias_1, @ctrlHostId_2, @arrayId_2, @alias_2
    
    SET	@errCode	= @@ERROR
	IF	@errCode != 0 GOTO CX_EXIT 

END   
CLOSE db_cursor    
DEALLOCATE db_cursor
SET @openCurFlag = 0

CX_EXIT:

IF @openCurFlag > 1
BEGIN
	CLOSE db_cursor
	DEALLOCATE db_cursor
END
IF @errCode != 0
	SELECT 2
ELSE
	SELECT 0

RETURN