sp_helpdevice (T-SQL)

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	varchar(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 3 or 4 = Disk dump device 5 = Tape device 0 = Database 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