SQL Server Indexes - verify naming convention

Question

I'm looking for a T-SQL script, which can verify that my indexes are named correctly.

PK_ for primary keys
UK_ for unique keys
IX_ for non clustered non unique indexes
UX_ for unique indexes

and furthermore, the naming of the index (the tricky part):

IX_Column1_Column2 - instead of IX_CrazyIndexWhichFixesPerformance

Someone out there with this type of script?

@HoneyBadger to me it looks like asker *has* a convention, and wants a script to *check it's being followed*... — AakashM, Oct 27 '17 at 09:50

ruskind · Answer 1 · 2017-10-27T13:19:06.060

It is quite easily accomplished with use of the Object Catalog Views (sys.*).

It depends how you want to approach this - you can write a full blown procedure to check all these things. I don't have code that will do exactly the above but this should set you on the right track.

For the Primary Key Check, you can find primary keys that don't fit your naming scheme with:

SELECT distinct 
i.name AS index_name,
i.object_id
from
sys.indexes i
where
i.is_primary_key = 1
and i.name not like 'PK_%'

It will be a question of using the sys.objects table to filter for the particular key constraint.

For columns, join through sys.index_columns and sys.columns:

SELECT distinct 
c.name AS column_name,
i.name AS index_name,
i.object_id
from
sys.indexes i
inner join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id

As you will want to concatenate column names into one field, you will need to look into FOR XML PATH('')

This information plus careful manipulation of the naming conventions you require should allow you perform these checks.

score 0 · Accepted Answer · answered Nov 02 '17 at 11:05

I ended up with this - it may not be pretty but gets the job done for now :-)

WITH indexesCTE
AS
(
    SELECT 
        t.name AS TableName
        , i.name AS IndexName 
        , i.index_id AS IndexId
        , ic.index_column_id AS ColumnId
        , t.object_id AS TableId
        , REPLACE(c.name, '_', '') AS ColumnName
        , i.object_id AS IndexObjectId
    FROM sys.indexes i 
    INNER JOIN sys.index_columns ic ON  i.object_id = ic.object_id and i.index_id = ic.index_id 
    INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id 
    INNER JOIN sys.tables t ON i.object_id = t.object_id 
    WHERE 
         i.is_primary_key = 0 
         AND i.is_unique = 0 
         AND i.is_unique_constraint = 0 
         AND t.is_ms_shipped = 0 
)
, indexNameProposal
AS
(
SELECT  i.TableName
        , i.IndexName AS CurrentIndexName
        , 'IX' + STUFF((SELECT '_' + i2.ColumnName FROM indexesCTE i2 WHERE i2.IndexId = i.IndexId AND i.TableId = i2.TableId FOR XML PATH('')),1,0,'') AS IndexNameProposal
        FROM indexesCTE i
        GROUP BY i.TableName, i.IndexName, i.TableId, i.IndexId
)
SELECT 
    *
    FROM indexNameProposal i
    WHERE i.CurrentIndexName <> i.IndexNameProposal

SQL Server Indexes - verify naming convention

2 Answers2