Help error message inside dynamic T-SQL

Question

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'.

Answer 1

I agree with Erik, but answering your question the error was in the quotation marks in the STUFF and here AND ISNULL (DUPE1.include_column_list, '') = ISNULL (DUPE2.include_column_list, '')

You did not have the quotation marks correctly, remember that when you intermix texts all the '' must be like '' ''

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 ,--BAD quotation 
 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,--BAD quotation 
 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, '''') --BAD quotation 
 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

Answer 2

Rather than hack away at a script you don't understand well enough to troubleshoot, why not use something tested and understandable, like sp_BlitzIndex?

If you run EXEC sp_BlitzIndex @Mode = 4, @GetAllDatabases = 1; it will go through and diagnose all sorts of index issues beyond what you're looking for in that Script You Found On The InternetTM