sp_helpdevice

Transact-SQL Reference

Transact-SQL Reference

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
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

See Also

sp_dropdevice

sp_helpdb