Collections:
List All Files in a Filegroup
How to List All physical Files in a logical Filegroup?
✍: FYIcenter.com
In a large SQL Server Database, you may have multiple logical Filegroups.
And a Filegroup may have multiple physical Files.
To list all physical Files in a logical Filegroup, you need to join two system tables, sys.filegroups and sys.database_files, using the data_space_id and identifier.
Here is query to count the number of physical Files in each logical Filegroup:
select g.data_space_id, count(*) as count, g.name from sys.filegroups g, sys.database_files f where g.data_space_id = f.data_space_id group by g.data_space_id, g.name data_space_id count name 1 2 DEFAULT 2 1 PRODUCT 3 4 ORDER
Here is a query to list the physical Files of the first Filegroup:
select g.data_space_id, g.name, F.* from sys.filegroups g, sys.database_files f where g.data_space_id = f.data_space_id and g.name = 'DEFAULT' data_space_id name file_id type_desc name physical_name ... 1 DEFAULT 1 ROWS PRIMARY D:\DATA\primary.mdf ... 1 DEFAULT 2 ROWS EXTENSION D:\DATA\extension.ndf ...
2019-07-09, 484👍, 0💬
Popular Posts:
How to valid UUID/GUID strings? In order to help your programming or testing tasks, FYIcenter.com ha...
How to access Response Status Code 400 as Success? My sampler failed, because the server returns the...
Where to find test data generators? FYIcenter.com has prepared a number of test data generators for ...
What are date and time test values? Date and time test values are frequently needed in testing date ...
How to see my IP address Host Name? To help you to see your IP Address Host Name, FYIcenter.com has ...