View: dbo.Index_Names | |||
View definition | |||
-- -- Lists all indexes on user-defined tables, along with a computed, standardized name. -- CREATE VIEW dbo.Index_Names AS WITH Exceptions AS ( SELECT * FROM ( -- Add exceptions to the standard naming rules by editing the VALUES clause below. -- -- When there are at least two real exceptions, -- please delete the silly example rows and this sentence. -- VALUES (N'JustAnIndex'), (N'AnotherIndex') ) AS t (excepted_index_name) ), IndexesWithKeyStrings AS ( SELECT i.*, CAST((SELECT N'$' + c.name AS [text()] FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE) as nvarchar(max)) AS key_columns_string FROM sys.indexes AS i WHERE i.index_id > 0 ), ComputedNames AS ( SELECT SCHEMA_NAME(t.schema_id) as schema_name, t.name AS table_name, i.name AS index_name, SUBSTRING(CASE WHEN i.is_primary_key = 1 THEN N'PK' WHEN i.is_unique = 1 THEN N'UK' ELSE N'IX' END -- For constraints, add the table name. Constraints are scoped at database level, -- at least according to the SQL standard. + CASE WHEN i.is_primary_key = 1 OR i.is_unique = 1 THEN N'_' + t.name ELSE N'' END -- Add the key column name lists, with each column column name prefixed by '$'. -- Omit the list from primary key names when they follow obvious conventions. + CASE WHEN i.is_primary_key = 1 AND LOWER(i.key_columns_string) = (N'$' + LOWER(t.name) + N'_key') THEN N'' ELSE i.key_columns_string END, 1, 128) AS computed_index_name FROM sys.tables AS t INNER JOIN IndexesWithKeyStrings AS i ON t.object_id = i.object_id WHERE t.is_ms_shipped = 0 -- exclude system tables ) SELECT C.schema_name, C.table_name, C.index_name, COALESCE(E.excepted_index_name, C.computed_index_name) AS computed_index_name FROM ComputedNames AS C LEFT OUTER JOIN Exceptions E ON C.index_name = E.excepted_index_name | |||