SQL: List of tables and indexes, with associated File Group

The script below (written using SQL Server 2005) 1, returns a list of all tables (along with the schema name), and indexes (where they exist), and the name of the associated file group.

This will useful to find out which objects have been assigned to the wrong file group.

I have a database with over 10 file groups, and wanted to find out why there was over 50Gb of data in the PRIMARY file group, even though there were only a couple of tables in that group. When I ran this script, I discovered there were some indexes that had been incorrectly created in the PRIMARY group!!!

SELECT  SCHEMA_NAME(T.Schema_ID) AS SchemaName,
		T.Name AS TableName,
		ISNULL(I.[name], 'N/A') AS IndexName,
		CASE    WHEN I.index_id = 0 THEN 'N/A'
				WHEN I.index_id = 1 THEN 'Clustered Index'
				WHEN I.index_id = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
				ELSE 'Non Clustered Index'
		END AS IndexType,
		F.name AS IndexFileGroupName
FROM sys.tables T WITH (NOLOCK)
LEFT OUTER JOIN sys.indexes I
	ON T.object_id = I.object_id
LEFT OUTER JOIN sys.filegroups F
	ON I.data_space_id = F.data_space_id
WHERE T.type = 'U'
ORDER BY SCHEMA_NAME(T.Schema_ID), T.Name
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.