T-SQL Query to list all indexes and their filegroups

I was fishing for a query to list all indexes and their FileGroups, so I could locate the ones that were created incorrectly and re-create them in their right place.

Ended up finding the query bellow at Technet by a Mr. Olaf  Helper. Mr. "Helper" was a great help indeed (ba dum tsss).

SELECT DS.NAME              AS DataSpaceName, 

       AU.type_desc         AS AllocationDesc, 

       AU.total_pages / 128 AS TotalSizeMB, 

       AU.used_pages / 128  AS UsedSizeMB, 

       AU.data_pages / 128  AS DataSizeMB, 

       SCH.NAME             AS SchemaName, 

       OBJ.type_desc        AS ObjectType, 

       OBJ.NAME             AS ObjectName, 

       IDX.type_desc        AS IndexType, 

       IDX.NAME             AS IndexName 

FROM   sys.data_spaces AS DS 

       INNER JOIN sys.allocation_units AS AU 

               ON DS.data_space_id = AU.data_space_id 

       INNER JOIN sys.partitions AS PA 

               ON ( AU.type IN ( 1, 3 ) 

                    AND AU.container_id = PA.hobt_id ) 

                   OR ( AU.type = 2 

                        AND AU.container_id = PA.partition_id ) 

       INNER JOIN sys.objects AS OBJ 

               ON PA.object_id = OBJ.object_id 

       INNER JOIN sys.schemas AS SCH 

               ON OBJ.schema_id = SCH.schema_id 

       LEFT JOIN sys.indexes AS IDX 

              ON PA.object_id = IDX.object_id 

                 AND PA.index_id = IDX.index_id 

ORDER  BY DS.NAME, 

          SCH.NAME, 

          OBJ.NAME, 

          IDX.NAME 

Here is the link to the original post on Technet: https://gallery.technet.microsoft.com/scriptcenter/c7483555-cc22-4f6c-b9c4-90811eb3bdb6

Using this query I was able to locate the indexes (clustered and nonclustered) that were incorrect and placed them (actually drop and re-create them into their righteous location). 

Although creating multiple files "per se" is not a performance improvement garantee, you need to test it in your case: http://community.idera.com/blog/b/community_blog/posts/increase-sql-server-performance-using-multiple-files. 

Messy work, but indeed necessary if your boss has OCD. You can create another FileGroup and add files to it using this:

ALTER DATABASE dbx ADD filegroup dbx_file_group; 

ALTER DATABASE dbx ADD FILE (NAME='dbx_file_group1', filename= 

'N:\MSSQL13.MDM\MSSQL\DATA\dbx_file_group1.ndf') TO dbx_file_group; 

In order to move a index to a new FileGroup, you need to drop and recreate it (yeah, messy indeed):

DROP INDEX [CIX_INDEX1] ON table1 WITH ( online = OFF ) 

go 

CREATE CLUSTERED INDEX [CIX_INDEX1] 

  ON table1 ( [col1] ASC ) 

  ON [dbx_file_group] 

go 

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06