February 18, 2014 at 10:30 am
Is there any script to compare index structure inlcuding (Name , key cols and included cols ) among two databases in different servers .
As if the structure mis matches I would like to drop and re-create .
I tried below script which outputs the structure but not sure how to do the comparison .
Two option I think possible :- (Please share your view or any script )
1. Convert to HEXAdecimal (I dont think I can compare hexadecimal cols as it require BIT wise comparison)
2. Convert to a single string and then compare.
SELECT '[' + Sch .name + '].[' + Tab. [name] + ']' AS TableName,
Ind .[name] AS IndexName,ind .type_desc AS Index_type,
SUBSTRING(( SELECT ', ' + AC .name
FROM sys .[tables] AS T
INNER JOIN sys. [indexes] I ON T .[object_id] = I.[object_id]
INNER JOIN sys. [index_columns] IC ON I.[object_id] = IC.[object_id]
AND I. [index_id] = IC .[index_id]
INNER JOIN sys. [all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC. [column_id] = AC.[column_id]
WHERE Ind. [object_id] = I.[object_id]
AND Ind. index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH ('') ), 2 , 8000) AS KeyCols ,
SUBSTRING(( SELECT ', ' + AC .name
FROM sys .[tables] AS T
INNER JOIN sys. [indexes] I ON T. [object_id] = I .[object_id]
INNER JOIN sys.[index_columns] IC ON I. [object_id] = IC .[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T. [object_id] = AC .[object_id]
AND IC. [column_id] = AC.[column_id]
whERE Ind. [object_id] = I.[object_id]
AND Ind. index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH ('') ), 2 , 8000) AS IncludeCols
FROM sys .[indexes] Ind INNER JOIN sys .[tables] AS Tab ON Tab .[object_id] = Ind.[object_id]
INNER JOIN sys. [schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
WHERE Tab. name = 'CCArd_primary'
-- uncomment to get single table indexes detail
ORDER BY TableName
February 18, 2014 at 11:35 am
You could tweak the script [Click Here][/url] to script out and store your indexes to a centralized table, then use a simple check to compare them (this is what I do here)...I'm not sure this is the simplest solution, but I think it would work
D ECLARE @Text1 nvarchar(max), @Text2 nvarchar(max)
SELECT @Text1 = IndexText from [NAS2-DBA1].IndexManagement.dbo.MasterIndexes
WHERE ServerName = 'Server1' AND DBName = 'Database' AND [Type] = 'NCI' AND IndexName = 'idx_IndexToCompare'
SELECT @Text2 = IndexText from [NAS2-DBA1].IndexManagement.dbo.MasterIndexes
WHERE ServerName = 'Server2' AND DBName = 'Database' AND [Type] = 'NCI' AND IndexName = 'idx_IndexToCompare'
IF BINARY_CHECKSUM(@Text1) <> BINARY_CHECKSUM(@Text2)
BEGIN
PRINT ' Discrepancy found'
END You could pretty easily automate the entire thing...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 18, 2014 at 11:54 am
Quick update. Use varchar(max), not nvarchar(max).
This will detect any change within each string
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 18, 2014 at 1:15 pm
Thanks , this helped 🙂 . I will check to add to loop through all the indexes in the table .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply