SQLDBA- how to know the created index file group name with T-SQL

 Hello, 

To determine the filegroup where an index is created in SQL Server, you can use the following methods:

1. Using sys.indexes and sys.filegroups System Views

You can query the system views to find the filegroup of an index:

Sql
SELECT 
    i.name AS IndexName,
    o.name AS TableName,
    f.name AS FileGroupName
FROM 
    sys.indexes i
JOIN 
    sys.objects o ON i.object_id = o.object_id
JOIN 
    sys.data_spaces f ON i.data_space_id = f.data_space_id
WHERE 
    o.type = 'U'; -- Only user tables

This query will list all indexes, their associated tables, and the filegroups they belong to.

2. Using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to your database.
2. Navigate to the table where the index is created.
3. Expand the Indexes folder under the table.
4. Right-click the index and select Properties.
5. In the Storage section, you can see the filegroup where the index is stored.

3. Using sp_helpindex

You can use the sp_helpindex system stored procedure to get details about indexes on a specific table:

Sql
EXEC sp_helpindex 'YourTableName';

This will return information about the indexes on the table, including their names and types. However, it does not directly show the filegroup. For filegroup details, use the first method.