List file groups and indexes for each tables

This is one of those quick tips and trick posts I do once in a while.  If you ever want to list the indexes and file groups for each table in your sql server database, you can use the following sql for that:

SELECT o.name as TableName, i.name as IndexName,  f.name as FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = ‘U’ ;

 

 

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in how to, Misc, sql server and tagged , , . Bookmark the permalink.