SQL Server如何找出数据库中没有索引的表
发布时间：2025-05-15 13:17:31
作者：益华网络
来源：undefined
浏览量（3）
点赞（3）
摘要：本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。 在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 ( NU, NV ) 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 = Nmicrosoft_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 ( NU, NV ) 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 = Nmicrosoft_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实例，运行上面脚本，将采集到的相关数据存储起来。
扫一扫，关注我们
声明：本文由【益华网络】编辑上传发布，转载此文章须经作者同意，并请附上出处【益华网络】及本页链接。如内容、图片有任何版权问题，请联系我们进行处理。
3
