在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闲思杂想录）：SQL Server如何找出数据库中没有索引的表
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/227872.html
