TSQL: List tables and their file groups

This query will return file groups names of all tables:

SELECT DISTINCT
SCHEMA_NAME(t.schema_id) AS [schema_name],
t.name AS table_name,
s.name AS fg_name
FROM
sys.partitions AS p join sys.allocation_units AS au ON p.partition_id = au.container_id
JOIN sys.data_spaces AS s ON au.data_space_id = s.data_space_id
JOIN sys.tables AS t ON p.object_id = t.object_id
WHERE
p.index_id < 2
ORDER BY
t.name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s