SQL Server如何找出數據庫中沒有索引的表

作者：瀟湘隱者 2021-05-08 14:07:26

在SQL Server數據庫的維護中，我們經常需要巡檢，找出一些沒有索引的表，然后根據實際情況判斷是否需要增加索引。

本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉載本文請聯系DBA閑思雜想錄公眾號。

在SQL Server數據庫的維護中，我們經常需要巡檢，找出一些沒有索引的表，然后根據實際情況判斷是否需要增加索引。下面分享一個腳本，如何找出當前數據庫中沒有索引的表信息。

/************************************************************************************************************** 
    --腳本名稱  ：      find_without_index_tables.sql 
    --腳本作者  :       瀟湘隱者 
    --創建日期  :       2016‎-10‎-‎27‎ 
*************************************************************************************************************** 
    腳本功能    ：      查找數據庫中沒有任何索引的表，并計算表的行數，用作是否創建索引的一個依據 
*************************************************************************************************************** 
    注意事項    :       需要切換到具體的用戶數據庫后執行腳本 
*************************************************************************************************************** 
    參考資料    :        
*************************************************************************************************************** 
    更新記錄    ：      2016‎-10‎-‎27‎：創建此腳本 
                      2020-03-14: 修改腳本，新增SERVER_NAME，DB_NAME 
***************************************************************************************************************/ 
SELECT DISTINCT 
        @@SERVERNAME              AS [SERVER_NAME] 
       ,DB_NAME()                 AS [DB_NAME] 
       ,so.object_id              AS [OBJECT_ID]  
       ,SCHEMA_NAME(so.schema_id) +'.' +OBJECT_NAME(so.object_id)  
                                  AS [TABLE_NAME]  
       ,MAX(dmv.rows)             AS [APPROXIMATE_ROWS]  
       ,MAX(d.ColumnCount)        AS [COLUMN_COUNT] 
FROM    sys.objects so ( NOLOCK ) 
        JOIN sys.indexes si ( NOLOCK ) ON so.object_id = si.object_id 
                                          AND so.type IN ( N'U', N'V' ) 
        JOIN sysindexes dmv ( NOLOCK ) ON so.object_id = dmv.id 
                                          AND si.index_id = dmv.indid 
        FULL OUTER JOIN ( SELECT    object_id , 
                                    COUNT(1) AS ColumnCount 
                          FROM      sys.columns (NOLOCK) 
                          GROUP BY  object_id 
                        ) d ON d.object_id = so.object_id 
WHERE   so.is_ms_shipped = 0 
        AND so.object_id NOT IN ( 
        SELECT  major_id 
        FROM    sys.extended_properties (NOLOCK) 
        WHERE   name = N'microsoft_database_tools_support' ) 
        AND INDEXPROPERTY(so.object_id, si.name, 'IsStatistics') = 0 
GROUP BY so.schema_id , 
        so.object_id 
HAVING  ( CASE OBJECTPROPERTY(MAX(so.object_id), 'TableHasClustIndex') 
            WHEN 0 THEN COUNT(si.index_id) - 1 
            ELSE COUNT(si.index_id) 
          END = 0 ) 
ORDER BY [APPROXIMATE_ROWS] DESC;

上面腳本只能查詢當前數據庫中沒有索引的表，我們知道，生產環境中，一個實例下面往往有多個用戶數據庫，我們需要采集每個數據庫中沒有索引的表信息，那么上面腳本明顯有點硬傷，所以，重寫了這個腳本。

/************************************************************************************************************** 
    --腳本名稱  ：      find_without_index_tables.sql 
    --腳本作者  :       瀟湘隱者 
    --創建日期  :       2016‎-10‎-‎27‎ 
*************************************************************************************************************** 
    腳本功能    ：      批量查找實例下面的每個數據庫，找出沒有任何索引的表 
*************************************************************************************************************** 
    注意事項    :       暫無 
*************************************************************************************************************** 
    參考資料    :        
*************************************************************************************************************** 
    更新記錄    ：      2016‎-10‎-‎27‎：創建此腳本 
                      2020-03-14: 修改腳本，新增SERVER_NAME，DB_NAME 
***************************************************************************************************************/ 
 
 
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#Database')) 
BEGIN 
    DROP TABLE #Database; 
END 
 
CREATE TABLE #Database (database_id INT  ,database_name NVARCHAR(128) ); 
 
 INSERT INTO #Database 
 SELECT database_id, name FROM sys.databases WHERE state_desc='ONLINE'  
 AND name NOT IN ('master','msdb','tempdb','model', 'distribution') 
 
 
DECLARE @database_name    NVARCHAR(128); 
DECLARE @database_id      INT; 
DECLARE @cmdText          NVARCHAR(MAX); 
 
SET @database_name =''; 
SET @database_id =1; 
 
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#TAB_NO_INDEX_INFO')) 
BEGIN 
    DROP TABLE #TAB_NO_INDEX_INFO; 
END 
 
CREATE TABLE #TAB_NO_INDEX_INFO( 
    [SERVER_NAME] [NVARCHAR](32) NULL, 
    [INSTANCE_NAME] [NVARCHAR](64) NULL, 
    [DATABASE_NAME] [NVARCHAR](32) NULL, 
    [TABLE_NAME] [NVARCHAR](128) NULL, 
    [OBJECT_ID] [INT] NULL, 
    [APPROXIMATE_ROWS] [INT] NULL, 
    [COLUMN_COUNT] [INT] NULL 
    ); 
 
WHILE(1=1) 
  
BEGIN 
    SELECT TOP 1 
            @database_id = database_id , 
            @database_name = database_name 
    FROM    #Database 
    WHERE   database_id > @database_id -- next database_name greater than @database_id 
    ORDER BY database_id -- database_id order  
  
     -- exit loop if no more name greater than the last one used 
    If @@rowcount = 0 Break  
 
    SET @cmdText='USE ' + @database_name  +'; 
         --GO 
         INSERT  INTO #TAB_NO_INDEX_INFO 
        ( SERVER_NAME , 
          INSTANCE_NAME , 
          DATABASE_NAME , 
          TABLE_NAME , 
          OBJECT_ID , 
          APPROXIMATE_ROWS , 
          COLUMN_COUNT 
        ) 
        SELECT  DISTINCT 
               CAST(SERVERPROPERTY(''MachineName'') AS NVARCHAR(32)) 
                                                    AS [SERVER_NAME]   , 
                @@SERVICENAME                       AS [INSTANCE_NAME] , 
                DB_NAME()                           AS [DATABASE_NAME] , 
                SCHEMA_NAME(so.schema_id)+ ''.'' +   
                OBJECT_NAME(so.object_id)           AS [TABLE_NAME]    , 
                so.object_id                        AS [OBJECT_ID]     , 
                MAX(dmv.rows)                       AS [APPROXIMATE_ROWS] , 
                MAX(d.ColumnCount)                  AS [COLUMN_COUNT] 
        FROM    sys.objects so ( NOLOCK ) 
                JOIN sys.indexes si ( NOLOCK ) ON so.object_id = si.object_id 
                                                  AND so.type IN ( N''U'', N''V'' ) 
                JOIN sysindexes dmv ( NOLOCK ) ON so.object_id = dmv.id 
                                                  AND si.index_id = dmv.indid 
                FULL OUTER JOIN ( SELECT    object_id , 
                                            COUNT(1) AS ColumnCount 
                                  FROM      sys.columns (NOLOCK) 
                                  GROUP BY  object_id 
                                ) d ON d.object_id = so.object_id 
        WHERE   so.is_ms_shipped = 0 
                AND so.object_id NOT IN ( 
                SELECT  major_id 
                FROM    sys.extended_properties (NOLOCK) 
                WHERE   name = N''microsoft_database_tools_support'' ) 
                AND INDEXPROPERTY(so.object_id, si.name, ''IsStatistics'') = 0 
        GROUP BY so.schema_id , 
                so.object_id 
        HAVING  ( CASE OBJECTPROPERTY(MAX(so.object_id), ''TableHasClustIndex'') 
                    WHEN 0 THEN COUNT(si.index_id) - 1 
                    ELSE COUNT(si.index_id) 
                  END = 0 ) 
        ORDER BY [APPROXIMATE_ROWS] DESC; '  
        PRINT @cmdText; 
 
 
        EXEC ( @cmdText); 
        --EXEC SP_EXECUTESQL @cmdText, N'@database_name NVARCHAR(32)',@database_name 
 
  
 Delete Db 
  From #Database Db WHERE database_id=@database_id; 
  
END 
 
SELECT  * 
FROM    #TAB_NO_INDEX_INFO 
ORDER BY APPROXIMATE_ROWS DESC; 
 
 
--找出數據量超過1000行沒有索引信息的表 
SELECT * FROM #TAB_NO_INDEX_INFO 
WHERE APPROXIMATE_ROWS > 1000 
ORDER BY APPROXIMATE_ROWS DESC

當你維護了很多SQL Server數據庫時，使用上面腳本到每臺SQL Server實例上跑一次，也是一件非常麻煩耗時的事情，所以還是需要自動化作業處理，定時使用Python腳本去每臺SQL Server實例上采集數據存儲下來，然后DBA只需做好兩件事情：監控采集數據和分析處理數據。這里就不貼Python腳本了，其實就是循環所有SQL Server實例，運行上面腳本，將采集到的相關數據存儲起來。

責任編輯：武曉燕來源： DBA閑思雜想錄