0

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

4

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
4

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 Internet™

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.