sp_helpfilegroup (Transact-SQL)

Returns the names and attributes of filegroups associated with the current database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_helpfilegroup [ [ @filegroupname = ] 'name' ]

Arguments

  • [ @filegroupname = ] 'name'
    Is the logical name of any filegroup in the current database. name is sysname, with a default of NULL. If name is not specified, all filegroups in the current database are listed and only the first result set shown in the Result Sets section is displayed.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Data type Description

groupname

sysname

Name of the filegroup.

groupid

smallint

Numeric filegroup identifier.

filecount

int

Number of files in the filegroup.

If name is specified, one row for each file in the filegroup is returned.

Column name Data type Description

file_in_group

sysname

Logical name of the file in the filegroup.

fileid

smallint

Numeric file identifier.

filename

nchar(260)

Physical name of the file including the directory path.

size

nvarchar(15)

File size in kilobytes.

maxsize

nvarchar(15)

Maximum size of the file.

This is the maximum size to which the file can grow. A value of UNLIMITED in this field indicates that the file grows until the disk is full.

growth

nvarchar(15)

Growth increment of the file. This indicates the amount of space added to the file every time new space is required.

0 = File is a fixed size and will not grow.

Permissions

Requires membership in the public role.

Examples

A. Returning all filegroups in a database

The following example returns information about the filegroups in the AdventureWorks sample database.

USE AdventureWorks;
GO
EXEC sp_helpfilegroup;
GO

B. Returning all files in a filegroup

The following example returns information for all files in the PRIMARY filegroup in the AdventureWorks sample database.

USE AdventureWorks;
GO
EXEC sp_helpfilegroup 'PRIMARY';
GO

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)
sp_helpfile (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)
sys.filegroups (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

Understanding Files and Filegroups

Help and Information

Getting SQL Server 2005 Assistance