本文转载自微信公众号《DBA闲思杂想》，作者潇湘隐士。转载本文请联系DBA杂念公众号。在SQLServer数据库的维护中，我们经常需要检查找出一些没有索引的表，然后根据实际情况判断是否加索引。下面分享一个脚本，如何找出当前数据库中没有索引的表信息。/***************************************************************************************************************************--脚本名称：find_without_index_tables.sql--脚本作者：潇湘隐士--创建时间：2016-10-27******************************************************************************************************************************脚本功能：在数据库中查找一个没有任何索引的表，并计算该表的行数，作为是否创建索引的依据*********************************************************************************************************************注意：需要切换到特定用户数据库***************************后执行脚本********************************************************************************************参考：************************************************************************************************************************更新记录：2016-10-27:创建此脚本2020-03-14：修改脚本，添加SERVER_NAME，DB_NAME*************************************************************************************************************************/SELECTDISTINCT@@SERVERNAMEAS[SERVER_NAME],DB_NAME()AS[DB_NAME],so.object_idAS[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]]FROMsys.objectsso(NOLOCK)JOINsys.indexessi(NOLOCK)ONso.object_id=si.object_idANDso.typeIN(N'U',N'V')JOINsysindexesdmv(NOLOCK)ONso.object_id=dmv.idANDsi.index_id=dmv.indidFULLOUTERJOIN(SELECTobject_id,COUNT(1)ASColumnCountFROMsys.columns(NOLOCK)GROUPBYobject_id)dONd.object_id=so.object_idWHEREso.is_ms_shipped=0ANDso.object_idNOTIN(SELECTmajor_idFROMsys.extended_properties(NOLOCK)WHEREname=N'microsoft_database_tools_support',(so.object.object_id_support')name,'IsStatistics')=0GROUPBYso.schema_id,so.object_idHAVING(CASEOBJECTPROPERTY(MAX(so.object_id),'TableHasClustIndex')WHEN0THENCOUNT(si.index_id)-1ELSECOUNT(si.index_id)END=0)ORDERBY[APPROXIMATE_ROWS]DESC;以上脚本只能查询当前数据库中没有索引的表。我们知道，在生产环境中，一个实例下往往会有多个用户数据库。我们需要收集每个数据库中没有索引的表信息，所以上面的脚本显然有点hardHurt，所以，重写了这个脚本/***************************************************************************************************************************--脚本名称：find_without_index_tables.sql--脚本作者：潇湘隐士--创建时间：2016-10-27******************************************************************************************************************************脚本功能：批量搜索实例下的各个数据库，找出没有索引的表*********************************************************************************************************************注：否***********************************************************************************************************************参考：********************************************************************************************************************更新记录：2016-10-27：创建此脚本2020-03-14：修改脚本，添加SERVER_NAME、DB_NAME****************************************************************************************************************************/IFEXISTS(SELECT*FROMtempdb.dbo.sysobjectsWHEREid=OBJECT_ID('tempdb.dbo.#Database'))开始ROPTABLE#Database;ENDCREATETABLE#Database(database_idINT,database_nameNVARCHAR(128));INSERTINTO#DatabaseSELECTdatabase_id,nameFROMsys.databasesWHEREstate_desc='ONLINE'ANDnameNOTIN('master','msdb','tempdb','model','distribution')声明@database_nameNVARCHAR(128);DECLARE@database_idINT;DECLARE@cmdTextNVARCHAR(MAX);SET@database_name='';SET@database_id=1;IFEXISTS(SELECT*FROMtempdb.dbo.sysobjectsWHEREid=OBJECT_ID('tempdb.dbo.#TAB_NO_INDEX_INFO'))BEGINDROPTABLE#TAB_NO_INDEX_INFO;ENDCREATETABLE#TAB_NO_INDEX_INFO([SERVER_NAME][NVARCHAR](32)NULL,[INSTANCE_NAME][NVARCHAR](64)NULL,[DATABASE_NAME][NVARCHAR](32)NULL,[TABLE_NAME][NVARCHAR](128)NULL,[OB??JECT_ID][INT]NULL,[APPROXIMATE_ROWS][INT]NULL,[COLUMN_COUNT][INT]NULL);同时(1=1)BEGINSELECTTOP1@database_id=database_id,@database_name=database_nameFROM#DatabaseWHEREdatabase_id>@database_id--nextdatabase_namegreaterthan@database_idORDERBYdatabase_id--database_idorder--exitloopifnomorenamegreaterthanthelastoneusedIf@@rowcount=0BreakSET@cmdText='USE'+@database_name+';--GOINSERTINTO#TAB_NO_INDEX_INFO(SERVER_NAME,INSTANCE_NAME,DATABASE_NAME,TABLE_NAME,OBJECT_ID,APPROXIMATE_ROWS,COLUMN_COUNT)SELECTDISTINCTCAST(SERVERPROPERTY(''MachineVARCH'AR(32MachineVARCH')ASN))AS[SERVER_NAME],@@SERVICENAMEAS[INSTANCE_NAME],DB_NAME()AS[DATABASE_NAME],SCHEMA_NAME(so.schema_id)+''.''+OBJECT_NAME(so.object_id)AS[TABLE_NAME],so.object_idAS[OBJECT_ID],MAX(dmv.rows)AS[APPROXIMATE_ROWS],MAX(d.ColumnCount)AS[COLUMN_COUNT]FROMsys.objectsso(NOLOCK)JOINsys.indexessi(NOLOCK)ONso.object_id=si.object_idANDso.typeIN(N''U'',N''V'')JOINsysindexesdmv(NOLOCK)ONso.object_id=dmv.idANDsi.index_id=dmv.indidFULLOUTERJOIN(SELECTobject_id,COUNT(1)ASColumnCountFROMsys.columns(NOLOCK)GROUPBYobject_id)dONd.object_id=so.object_idWHEREso。is_ms_shipped=0ANDso.object_idNOTIN(SELECTmajor_idFROMsys.extended_properties(NOLOCK)WHEREname=N''microsoft_database_tools_support'')ANDINDEXPROPERTY(so.object_id,si.name,''IsStatistics'')=0GROUPBYso.schema_id,so.object_idHAVING(CASEOBJECTPROPERTY(MAX(so.object_id),''TableHasClustIndex'')WHEN0THENCOUNT(si.index_id)-1ELSECOUNT(si.index_id)END=0)ORDERBY[APPROXIMATE_ROWS]DESC;'PRINT@cmdText;EXEC(@cmdText);--EXECSP_EXECUTESQL@cmdText,N'@database_nameNVARCHAR(32)',@database_nameDeleteDbFrom#DatabaseDbWHEREdatabase_id=@database_id;ENDSELECT*FROM#TAB_NO_INDEX_INFOORDERBYAPPROXIMATE_ROWSDESC;--找出数量dataexceeds1000rowsoftableswithoutindexinformationSELECT*FROM#TAB_NO_INDEX_INFOWHEREAPPROXIMATE_ROWS>1000ORDERBYAPPROXIMATE_ROWSDESC当你维护很多SQLServer数据库时，使用上面的脚本在每个SQLServer实例上运行一次也是一件很麻烦和耗时的事情，所以仍然需要自动化作业处理，定期使用Python脚本在每个SQLServer实例上收集数据并存储，然后DBA只需要做两件事：监控收集到的数据和分析处理数据。Python脚本这里就不贴了，其实就是循环所有的SQLServer实例，运行上面的脚本，把收集到的相关数据存储起来。
