I have a requirement to come up with reporting for indexes for our whole production environment:
I found the below script online and modified it according to my requirement and trying to execute it using cursor (I have also tried sp_MSForEachDb) to get result from all databases in the instance. The script should show all Exact Duplicate indexes in a particular database.
Although I have wrapped my query in double quotes but I keep getting too many errors.
If I run the script without loop it returns the result correctly.
Please see the script and errors I'm getting below the script. I have been struggling with this for the past few days and have looked exhaustively online here and many other posts but can;t figure this out.
I will need to send the result in email body to our distribution list for all servers. so, I will appreciate if someone has a better idea on how to establish this.
Script:
DECLARE @db_name AS nvarchar(max)
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN('master', 'model', 'msdb', 'tempdb') and state <> 1
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
IF OBJECT_ID('tempdb..#IndexTemp') IS NOT NULL DROP Table #IndexTemp --If exist drop the temp table.
EXEC('
Begin
USE ' + '[' + @db_name + ']' + '
;WITH CTE_INDEX_DATA AS (
SELECT
SCHEMA_DATA.name AS schema_name,
TABLE_DATA.name AS table_name,
INDEX_DATA.name AS index_name,
STUFF((SELECT '', '' + COLUMN_DATA_KEY_COLS.name + '' '' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN ''DESC'' ELSE ''ASC'' END -- Include column order (ASC / DESC)
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR XML PATH('')), 1, 2, '') AS key_column_list ,
STUFF(( SELECT '', '' + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS
ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS
ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR XML PATH('')), 1, 2, '') AS include_column_list,
INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA
ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA
ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND INDEX_DATA.type_desc IN (''NONCLUSTERED'', ''CLUSTERED'')
)
--Insert all records into a temp table #IndexTemp with appropriate filters:
SELECT * INTO #IndexTemp
FROM CTE_INDEX_DATA DUPE1
WHERE EXISTS
(SELECT * FROM CTE_INDEX_DATA DUPE2
WHERE DUPE1.schema_name = DUPE2.schema_name
AND DUPE1.table_name = DUPE2.table_name
AND DUPE1.key_column_list = DUPE2.key_column_list
AND ISNULL(DUPE1.include_column_list, '') = ISNULL(DUPE2.include_column_list, '')
AND DUPE1.index_name <> DUPE2.index_name)
AND INDEX_NAME NOT LIKE (''%PK%'')
--Return duplicate tbale_names only
SELECT * from #IndexTemp WHERE table_name IN
(SELECT table_name FROM #IndexTemp GROUP BY table_name HAVING COUNT(*) > 1)
ORDER BY table_name
END')
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
For each database, I get these errors:
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'ORDER'.
Msg 4145, Level 15, State 1, Line 59
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
