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 - http://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.
