Shulou(Shulou.com)05/31 Report--

This article mainly explains "SQL Server how to find the table without index in the database", the content of the explanation in the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "SQL Server how to find the table without index in the database"!

In the maintenance of SQL Server database, we often need to patrol to find out some tables without indexes, and then judge whether we need to add indexes according to the actual situation. Let's share a script on how to find information about tables that are not indexed in the current database.

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 (Null U') Nasty 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 = 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

The above script can only query tables in the current database that do not have an index. We know that in a production environment, there are often multiple user databases under an instance, and we need to collect information about tables that do not have an index in each database. So the above script is obviously a bit hard, so I rewrote the script.

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); 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; 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 (1x 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 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) 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 -- find tables with more than 1000 rows of data without index information SELECT * FROM # TAB_NO_INDEX_INFO WHERE APPROXIMATE_ROWS > 1000 ORDER BY APPROXIMATE_ROWS DESC

When you maintain a lot of SQL Server databases, using the above script to run once on each SQL Server instance is also a very troublesome and time-consuming thing, so you still need to automate job processing, regularly use Python scripts to collect data on each SQL Server instance and store it, and then DBA only needs to do two things: monitoring data collection and analyzing and processing data. Instead of posting the Python script here, it just loops through all the SQL Server instances, runs the above script, and stores the relevant data collected.

Thank you for your reading, the above is the content of "how SQL Server finds tables without indexes in the database". After the study of this article, I believe you have a deeper understanding of how SQL Server finds tables without indexes in the database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.