sp_helpdevice
Reports information about Microsoft® SQL Server™ database files. sp_helpdevice is used for backward compatibility with earlier versions of SQL Server that used the term device for a database file.
Syntax
sp_helpdevice [ [ @devname= ] 'name' ]
Arguments
[@devname=] 'name'
Is the name of the device for which to provide information. name is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column name | Data type | Description |
|---|---|---|
| device_name | sysname | Device name (or file name). |
| physical_name | nvarchar(46) | Physical file name. |
| description | nvarchar(255) | Description of the device. |
| status | int | A number that corresponds to the status description in the description column. |
| cntrltype | smallint | Controller number of the device:
2 = Hard disk device |
| size | int | Device size in 2 kb pages. |
Remarks
If name is specified, sp_helpdevice displays information about the specified database device or dump device. If name is not specified, sp_helpdevice displays information about all database devices and dump devices in master.dbo.sysdevices.
Old style database devices are added to the system with the DISK INIT statement. Dump devices are added to the system by sp_addumpdevice.
The device_number column is 0 for dump devices, 0 for the MASTER database device, and a value from 1 through 255 for other database devices.
Permissions
Execute permissions default to the public role.
Examples
This example reports information about all database devices and dump devices on SQL Server.
sp_helpdevice