What Is sys.filegroups?

Q

What Is sys.filegroups view in SQL Server?

✍: FYIcenter.com

A

sys.filegroups is a system view in a SQL Server Database that contains information about each logical filegroups used by the database.

Each record in the sys.filegroups table represents a single SQL Server Logical Filegroup with the following key fields:

  • name - sysname: Name of data space, unique within the database.
  • data_space_id - int: Data space ID number, unique within the database.
  • type - char(2): Data space type.
  • type_desc - nvarchar(60): Description of data space type:
  • is_default - bit: 1 = This is the default data space. The default data space is used when a filegroup or partition scheme is not specified in a CREATE TABLE or CREATE INDEX statement.
  • is_system - bit: Applies to: SQL Server 2012 (11.x) through SQL Server 2017.
  • filegroup_guid - uniqueidentifier: GUID for the filegroup.

Here is an example of SQL Server Database with only 1 required default filegroup:

SELECT * FROM sys.filegroups

name     data_space_id  type  type_desc       is_default  is_system  ...
DEFAULT  1              FG    ROWS_FILEGROUP  1           0          ...

 

List All Files in a Filegroup

What Is a SQL Server Filegroup

SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials

2019-07-14, 187👍, 0💬