The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.

Please help me with query to find list of indexes on the tables on which the views has been created.

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
ORDER BY TableName

• Edited by VIMD 1 hour 29 minutes ago

Try this if it works for you ...

Declare @viewName varchar(100)='schema.viewname'

Declare @tbls table(name varchar(100),type varchar(50),updated varchar(10),selected varchar(10),columnname varchar(100))
insert into @tbls
exec sp_depends @viewName

Select distinct a.name Table_name,i.name Index_Name 
from @tbls a 
 join  sys.tables t on a.name like '%'+t.name
join  sys.indexes i
on t.object_id=i.object_id

Thanks for your post

But its not giving any results , I want to find the tables and Indexes used by View(I mean dependent)