Script to Retrieve Tables, Indexes, Files and File Groups Information

Leave a comment (3) Go to comments

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


Script to Retrieve Tables, Indexes, Files and File Groups Information Verify that no Index should be placed on PRIMARY Filegroup sql server Where those Index are located Script to Retrieve Tables Indexes find Indexes which are created on a table Files and File Groups Information discover that no Index should be placed on PRIMARY Filegroup check Where those Index are located

EOF - Script to Retrieve Tables, Indexes, Files and File Groups Information, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

  1. Cool script, really saved atleast 3-4 hours of my time :)
    Thanks !!!

  2. Hi All,

    it’s a good script to view table and index allocated to wchich file name.

    Regards
    Jayant dass

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.