您好,登錄后才能下訂單哦!
這篇文章主要講解了“SQL Server怎么找出數(shù)據(jù)庫中沒有索引的表”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“SQL Server怎么找出數(shù)據(jù)庫中沒有索引的表”吧!
在SQL Server數(shù)據(jù)庫的維護(hù)中,我們經(jīng)常需要巡檢,找出一些沒有索引的表,然后根據(jù)實(shí)際情況判斷是否需要增加索引。下面分享一個(gè)腳本,如何找出當(dāng)前數(shù)據(jù)庫中沒有索引的表信息。
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;
上面腳本只能查詢當(dāng)前數(shù)據(jù)庫中沒有索引的表,我們知道,生產(chǎn)環(huán)境中,一個(gè)實(shí)例下面往往有多個(gè)用戶數(shù)據(jù)庫,我們需要采集每個(gè)數(shù)據(jù)庫中沒有索引的表信息,那么上面腳本明顯有點(diǎn)硬傷,所以,重寫了這個(gè)腳本。
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; --找出數(shù)據(jù)量超過1000行沒有索引信息的表 SELECT * FROM #TAB_NO_INDEX_INFO WHERE APPROXIMATE_ROWS > 1000 ORDER BY APPROXIMATE_ROWS DESC
當(dāng)你維護(hù)了很多SQL Server數(shù)據(jù)庫時(shí),使用上面腳本到每臺(tái)SQL Server實(shí)例上跑一次,也是一件非常麻煩耗時(shí)的事情,所以還是需要自動(dòng)化作業(yè)處理,定時(shí)使用Python腳本去每臺(tái)SQL Server實(shí)例上采集數(shù)據(jù)存儲(chǔ)下來,然后DBA只需做好兩件事情:監(jiān)控采集數(shù)據(jù)和分析處理數(shù)據(jù)。這里就不貼Python腳本了,其實(shí)就是循環(huán)所有SQL Server實(shí)例,運(yùn)行上面腳本,將采集到的相關(guān)數(shù)據(jù)存儲(chǔ)起來。
感謝各位的閱讀,以上就是“SQL Server怎么找出數(shù)據(jù)庫中沒有索引的表”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對(duì)SQL Server怎么找出數(shù)據(jù)庫中沒有索引的表這一問題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。