----------------------------------------------------------------------------------------------------------------------------------------------------
  
-- DATABASE AUDIT SCRIPTS - INDEXING
-- Created By: Matthew Senz
-- Description: Returns an audit and evaluation of the indexing state of the selected database
-- Website: http://help.senzdigital.com
  
----------------------------------------------------------------------------------------------------------------------------------------------------

-- Use Database Declaration (Please set before running)
USE AdventureWorks2017;

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000) = (SELECT DB_NAME());

SELECT  SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as [Clustered Index Count],
		SUM(CASE WHEN I.[type_desc]='HEAP' THEN 1 ELSE 0 END) as [Heap Count],
		ROUND(CAST(SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as real)/COUNT(1)*100,2) as [Percentage Indexed],
		CASE  
			WHEN ROUND(CAST(SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as real)/COUNT(1)*100,0)<50 THEN 'Very Bad'
			WHEN ROUND(CAST(SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as real)/COUNT(1)*100,0) BETWEEN 50 AND 70 THEN 'Bad'
			WHEN ROUND(CAST(SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as real)/COUNT(1)*100,0) BETWEEN 71 AND 98 THEN 'Good'
			WHEN ROUND(CAST(SUM(CASE WHEN I.[type_desc]='CLUSTERED' THEN 1 ELSE 0 END) as real)/COUNT(1)*100,0)>98 THEN 'Excellent'
		END as [Indexing Evaluation]
FROM [sys].[tables] AS T
INNER JOIN [sys].[indexes] AS I ON I.[object_id] = T.[object_id] and I.[index_id] < 2
LEFT OUTER JOIN [sys].[schemas] AS S ON T.[schema_id] = S.[schema_id]
LEFT OUTER JOIN [sys].[data_spaces] AS D ON D.[data_space_id] = I.[data_space_id]
;

SELECT  S.[name] as [Schema],
        T.[name] as [Table],
        I.[type_desc] as [Index]
FROM [sys].[tables] AS T
INNER JOIN [sys].[indexes] AS I ON I.[object_id] = T.[object_id] and I.[index_id] < 2
LEFT OUTER JOIN [sys].[schemas] AS S ON T.[schema_id] = S.[schema_id]
LEFT OUTER JOIN [sys].[data_spaces] AS D ON D.[data_space_id] = I.[data_space_id]
;