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