February 14, 2017 at 6:48 pm
I need a Script to list objects by file group/file.
Does anyone know of one?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2017 at 9:49 pm
Because each index in each object may be on a separate filegroup or partition scheme, drive your query off of sys.indexes, and join the data_space_id column to sys.filegroups.data_space_id.
Here's one I use that also throws in rowcounts and space used: SELECT schema_name(o.schema_id) + '.' + o.name AS [Table], IsNull(i.name, 'HEAP') AS [Index], fg.name AS [FGName],
sum(row_count) AS rows,
(sum(in_row_used_page_count) * 8.0) / 1024/1024 AS [Data GB],
(sum(lob_used_page_count) * 8.0) / 1024/1024 AS [LOB GB],
(sum(used_page_count) * 8.0) / 1024/1024 AS [Used GB],
(sum(reserved_page_count) * 8.0) / 1024/1024 AS [Reserved GB]
FROM sys.dm_db_partition_stats s INNER JOIN
sys.objects o ON s.object_id = o.object_id INNER JOIN
sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id INNER JOIN
sys.filegroups fg ON i.data_space_id = fg.data_space_id
WHERE o.is_ms_shipped = 0
GROUP BY o.schema_id, o.name, i.name, fg.name
ORDER BY [FGName], [Table]
GO
If you're using partitioning, then you'll need to LEFT JOIN sys.filegroups instead of INNER JOIN, and you'll also need to LEFT JOIN to sys.partition_schemes.data_space_id to get the partition scheme name.
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy