Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.
sp_helpfile [[@filename =] 'name']
0 (success) or 1 (failure)
| Column name | Data type | Description |
|---|---|---|
| name | sysname | Logical file name. |
| fileid | smallint | Numeric identifier of the file. |
| filename | nchar(260) | Physical file name. |
| filegroup | sysname | Group to which the file belongs. Database files can be grouped in file groups for allocation and administration purposes. Log files are never a part of a file group. |
| size | nvarchar(18) | File size. |
| maxsize | nvarchar(18) | Maximum size to which the file can grow. UNLIMITED value in this field indicate that the file grows until the disk is full. |
| growth | nvarchar(18) | Growth increment of the file. This indicates the amount of space added to the file each time new space is needed. |
| usage | varchar(9) | Usage of the file. For a data file, the usage is data only and for the log file the usage is log only. |
Execute permission defaults to the public role.
This example returns information about the files in pubs.
USE pubs
EXEC sp_helpfile
| sp_attach_db | sp_helpfilegroup |
| sp_attach_single_file_db | System Stored Procedures |
| sp_detach_db |