I’m having a little fun with documenting basic information about indexes in my current project. I’m posting the scripts here mostly for me to come back when I need them in the future, but hopefully some of you might find them useful.
Find tables without any index:
This script gives you the list of tables that don’t have any index.
; WITH A
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(t.OBJECT_ID) ,
TableName = t.name
FROM sys.tables t
WHERE OBJECTPROPERTY(t.OBJECT_ID,'TableHasIndex') = 0
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM A
Find tables without a clustered index:
This script gives the list of tables without a clustered index.
; WITH B
AS
(
SELECT SchemaName = OBJECT_SCHEMA_NAME(OBJECT_ID) ,
TableName = OBJECT_NAME(OBJECT_ID)
FROM sys.indexes
WHERE index_id = 0
AND OBJECTPROPERTY(OBJECT_ID, 'IsUserTable') = 1
)
SELECT SchemaName ,
TableName ,
TwoPartName = SchemaName+'.'+TableName
FROM B
ORDER BY SchemaName
Find indexes on a table along with columns covered:
Use this script to get the index names and a comma separated list of columns included in the index.
; WITH C
AS
(
SELECT TableName = t.name
, IndexName = i.name
, ColumnName = c.name
FROM sys.tables t
INNER JOIN
sys.indexes i
ON t.OBJECT_ID = i.OBJECT_ID
INNER JOIN
sys.index_columns ic
ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
ON c.OBJECT_ID = t.OBJECT_ID
AND c.column_id = ic.column_id
)
SELECT DISTINCT TableName, IndexName, ColumnNames
FROM cte A
CROSS APPLY
(
SELECT ColumnName + ', '
FROM cte B
WHERE A.TableName = B.TableName AND A.IndexName = B.IndexName
ORDER BY TableName, IndexName
FOR XML PATH('')
)D (ColumnNames)
Notice that I generously used CTE’s here. I like to reuse column aliases instead of repeating length expressions everywhere. CTE allows you to do that.
Sam Vanga 