Sql-Server

動態 T-SQL 中的幫助錯誤消息

  • September 24, 2019

我需要為我們的整個生產環境提供索引報告：

我在網上找到了以下腳本並根據我的要求對其進行了修改，並嘗試使用游標執行它（我也嘗試過 sp_MSForEachDb）以從實例中的所有數據庫中獲取結果。該腳本應顯示特定數據庫中的所有 Exact Duplicate 索引。

儘管我已將查詢用雙引號括起來，但我不斷收到太多錯誤。

如果我在沒有循環的情況下執行腳本，它會正確返回結果。

請查看我在腳本下方遇到的腳本和錯誤。在過去的幾天裡，我一直在為此苦苦掙扎，並且在這里 和許多其他文章中詳盡地查看了線上資訊，但無法弄清楚這一點。

我需要將結果以電子郵件正文的形式發送到我們所有伺服器的分發列表。所以，如果有人對如何建立這個有更好的想法，我將不勝感激。

腳本：

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

對於每個數據庫，我都會收到以下錯誤：

消息 156，級別 15，狀態 1，第 24 行

關鍵字“AS”附近的語法不正確。

消息 156，級別 15，狀態 1，第 38 行

關鍵字“ORDER”附近的語法不正確。

消息 4145，級別 15，狀態 1，第 59 行

在預期條件的上下文中指定的非布爾類型表達式，靠近“與”。

我同意Erik的觀點，但在回答你的問題時，錯誤出現在 STUFF 和此處的引號中 AND ISNULL (DUPE1.include_column_list, ‘’) = ISNULL (DUPE2.include_column_list, ‘’)

您沒有正確使用引號，請記住，當您混合文本時，所有的 ’’ 必須像 ’’ '’

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

引用自：https://dba.stackexchange.com/questions/249520

相關問答

Sql-Server

基於兩列創建唯一索引（雙向）

  • March 9, 2022
檢視問答
Sql-Server

聚集索引和排序

  • October 3, 2021
檢視問答
Sql-Server

如何使用 TSQL 動態更改數據庫

  • September 22, 2021
檢視問答
Sql-Server

截斷和插入：堆並建立索引或直接進入聚群表

  • March 23, 2021
檢視問答
Sql-Server

跨數據庫的索引使用情況

  • January 14, 2021
檢視問答
Sql-Server

有沒有辦法在 TSQL 中生成表創建腳本？

  • December 18, 2020
檢視問答