Behind the scene
There was a story when I created this script. There was an issue on our production server. After investigated, there was no index on one table but the table has an index on testing environment. Looks like when published the table, the DBA missed publish the index. After this issue, my supervisor asked me to create a script for listing all tables that do not have an index. Every table should be have an index obviously. Below script could not run on SQL Server 2000 and previous version.
Script
Below the script for list index availability of the tables.
Result of the script
Column Name | Description |
TableName | Name of the table |
SchemaName | Name of the schema |
HasIndex | Yes if the table has an index vice versa |
IndexName | Name of the index |
IndexKeys | Keys of the index |
IsPrimaryKey | Is this index a primary key? |
IndexType | Type of the index whether clustered or non clustered |
/* Created by : Solihin ho - https://solihinho.wordpress.com Compatibility : SQL Server 2005 and next */ DECLARE @ObjectID INT, @IndexID INT DECLARE @ObjectName SYSNAME, @SchemaName SYSNAME DECLARE @ColumnName SYSNAME DECLARE @IndexKeys NVARCHAR(1000) IF OBJECT_ID ('tempdb..#Result') IS NOT NULL DROP TABLE #Result CREATE TABLE #Result ( ObjectID INT, ObjectName SYSNAME, SchemaName SYSNAME, IndexId INT, IndexName SYSNAME NULL, IndexType NVARCHAR(60), IndexKeys NVARCHAR(1000), HasIndex VARCHAR(3), IsPrimaryKey BIT ) INSERT INTO #Result (ObjectID, ObjectName, SchemaName, IndexId , IndexName, IndexType, HasIndex, IsPrimaryKey) SELECT o.object_id, o.name AS ObjectName , s.name as SchemaName , i.index_id, i.name AS IndexName , i.type_desc , 'No' , i.is_primary_key FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.indexes i ON o.object_id = i.object_id WHERE o.type = 'U' --Index Keys IF (OBJECT_ID('tempdb..#keys_temp') IS NOT NULL) DROP TABLE #keys_temp SELECT r.ObjectID, r.IndexId, c.name AS ColumnName INTO #keys_temp FROM #Result r LEFT OUTER JOIN sys.index_columns ic ON r.ObjectID = ic.object_id AND r.IndexID = ic.index_id LEFT OUTER JOIN sys.columns c ON c.column_id = ic.column_id AND r.ObjectID = c.object_id WHERE r.IndexID > 0 WHILE (1 = 1) BEGIN SET @IndexKeys = '' SET @ObjectID = NULL SET @IndexID = NULL SELECT TOP 1 @ObjectID = ObjectID, @IndexID = IndexID FROM #keys_temp ORDER BY ObjectID, IndexID IF @ObjectID IS NULL GOTO _Loop1 WHILE (1 = 1) BEGIN SET @ColumnName = NULL SELECT TOP 1 @ColumnName = ColumnName FROM #keys_temp WHERE ObjectID = @ObjectID AND IndexID = @IndexID ORDER BY ColumnName IF @ColumnName IS NULL GOTO _Loop2 SET @IndexKeys = @IndexKeys + @ColumnName + ', ' DELETE FROM #keys_temp WHERE ObjectID = @ObjectID AND IndexID = @IndexID AND ColumnName = @ColumnName END _Loop2: UPDATE #Result SET IndexKeys = LEFT(@IndexKeys, LEN(@IndexKeys)-1) WHERE ObjectID = @ObjectID AND IndexID = @IndexID END _Loop1: DROP TABLE #keys_temp IF OBJECT_ID('tempdb..#object_temp') IS NOT NULL DROP TABLE #object_temp SELECT DISTINCT ObjectName, SchemaName INTO #object_temp FROM #Result WHILE (1 = 1) BEGIN SET @ObjectName = NULL SELECT TOP 1 @ObjectName = ObjectName, @SchemaName = SchemaName FROM #object_temp ORDER BY ObjectName, SchemaName IF @ObjectName IS NULL GOTO _Loop3 IF EXISTS (SELECT * FROM #Result WHERE ObjectName = @ObjectName AND SchemaName = @SchemaName AND IndexID > 0) BEGIN UPDATE #Result SET HasIndex = 'Yes' WHERE ObjectName = @ObjectName AND SchemaName = @SchemaName END DELETE FROM #object_temp WHERE ObjectName = @ObjectName AND SchemaName = @SchemaName END _Loop3: SELECT ObjectName AS TableName , SchemaName , HasIndex , IndexName , IndexKeys , CASE WHEN IsPrimaryKey = 1 Then 'Yes' ELSE 'No' END AS IsPrimaryKey , IndexType FROM #Result ORDER BY ObjectName, SchemaName |
This is the result when I ran the script on AdventureWorks database.