SQL Server Training | Tutorials | How to's

Today, I was working on one of the IO Performance issue. During analysis, I need to figure out index distribution across various Database Files and FileGroups. Thus, My objective is to discover that no Index should be placed on PRIMARY Filegroup.

So, I wanted to have information about

• Indexes which are created on a table
• Where those Index are located
• Verify that no Index should be placed on PRIMARY Filegroup

So here is the Script to meet this objective.

-- /***********************************************************************************
-- ** Description         :    Retrieve Tables, Indexes, Files, and File Groups Information.
-- *                        Script that lists information regarding tables, -- indexes,
--                         file groups and file names. 
-- * SQL Versions        :    2005 +
-- ***********************************************************************************/

select 'table_name'=object_name(i.id)
        ,i.indid
        ,'index_name'=i.name
        ,i.groupid
        ,'filegroup'=f.name
        ,'file_name'=d.physical_name
        ,'dataspace'=s.name
from    sys.sysindexes i
        ,sys.filegroups f
        ,sys.database_files d
        ,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go

OUTPUT