Within lifecycle services for production performance troubleshooting it is essential to get insights to the actual index design and usage from production. Samples: - To avoid suggesting indexes, that are mostly already there (just some additional included columns missing) - To avoid adding too many indexes, while existing once are not used anymore (because product and usage is changing) So, what if, we would get a lookup (freetextentry incl. SQL schema, because some tables exist in ax and dbo) of a table (e. g. dbo.INVENTTRANS) and then an output from below query: SELECT OBJECT_NAME(i.object_id) AS [TABLE NAME], i.name AS [INDEX NAME], i.type_desc +CASE WHEN is_unique = 1 THEN ', UNIQUE' ELSE '' END +CASE WHEN is_primary_key = 1 THEN ', PRIMARY KEY' ELSE '' END +CASE WHEN has_filter = 1 THEN ', FILTERED' ELSE '' END AS [INDEX DESCRIPTION], STUFF((SELECT ','+ COL_NAME(ic1.object_id,ic1.column_id) FROM sys.index_columns AS ic1 WHERE i.object_id = ic1.object_id AND i.index_id = ic1.index_id AND ic1.is_included_column=0 ORDER BY ic1.key_ordinal FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') AS [INDEX COLUMNS], STUFF((SELECT ','+ COL_NAME(ic2.object_id,ic2.column_id) FROM sys.index_columns AS ic2 WHERE i.object_id = ic2.object_id AND i.index_id = ic2.index_id AND ic2.is_included_column=1 ORDER BY ic2.key_ordinal FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') AS [INCLUDED COLUMNS], USER_SEEKS as [USER SEEKS], USER_SCANS AS [USER SCANS], USER_LOOKUPS AS [USER LOOKUPS], USER_UPDATES AS [USER UPDATES], LAST_USER_SEEK AS [LAST USER SEEK], LAST_USER_UPDATE AS [LAST USER UPDATE] FROM sys.indexes AS i LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1 AND i.object_id = OBJECT_ID('dbo.INVENTTRANS') ORDER BY OBJECT_NAME(i.object_id), i.index_id, i.name

Needs Votes
Ideas Administrator

Thank you for your feedback.

Currently this is not in our roadmap; however, we are tracking it and if we get more feedback and votes, we may consider it in the future.