SQL Server Blog

As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.

This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.

SELECT
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
   I.[name] AS [Index Name],
   S.[user_seeks] AS [User Seeks],
   S.[user_scans] AS [User Scans],
   S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
   ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
   ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
   ON T.[schema_id] = SC.[schema_id]
WHERE
   OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
   OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
   S.[database_id] = DB_ID(DB_NAME())
ORDER BY
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]

Just a quick script to list all schema’s, tables and column information from within a database.

SELECT
   (S.[name] + ‘.’ + T.[name]) AS [Table],
   C.[name] AS [Column Name],
   D.[name] AS [Date Type],
   C.max_length AS [Length],
   C.precision AS [Precision],
   C.scale AS [Scale],
   C.is_nullable AS [Nullable],
   C.is_identity AS [Identity Column],
   C.[collation_name] AS [Collation]
FROM sys.tables T INNER JOIN sys.schemas S
   ON T.schema_id = S.schema_id
INNER JOIN sys.columns C
   ON T.object_id = C.object_id
INNER JOIN sys.types D
   ON C.system_type_id = D.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.[name], C.column_id