sp_helpdevice System Stored Procedure

Reports information about SQL Server database devices and dump devices.

Syntax

sp_helpdevice [logical_name]

where

logical_name

Is the logical name of the database device or dump device that you want information about.

Remarks

When logical_name is specified, sp_helpdevice displays information about the specified database device or dump device. When no parameter is specified, sp_helpdevice displays information about all database devices and dump devices in master.dbo.sysdevices.

The sysdevices table contains database devices and dump devices. Database devices can be designated as default database devices, which means that they can be used for database storage when a user executes the CREATE DATABASE or ALTER DATABASE statement with no specifier database device name or with the keyword DEFAULT. To make a database device a default database device, execute the sp_diskdefault system procedure.

Database devices are added to the system with the DISK INIT statement. Dump devices are added to the system by the sp_addumpdevice system procedure.

The status column in the sp_helpdevice report contains a number that corresponds to the status description in the description column.

The cntrltype column specifies the controller number of the device. For hard-disk dump devices, the controller number is 2; for diskette dump devices, the controller number is 3 or 4; and for tape devices, the controller number is 5. For database devices, it is 0.

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.

The low and high columns represent virtual page numbers for database devices and disk dump devices.

The high and low columns represent media capacity for diskette dump devices (high - low = number of 2K blocks).

Examples

A.    Help on All Devices

This example reports information about all database devices and dump devices on SQL Server.

sp_helpdevice

B.    Help on a Single Device

This example reports information about the TAPEDUMP device.

sp_helpdevice TAPEDUMP

Permission

Execute permission defaults to the public group.

Tables Used

master.dbo.spt_values, master.dbo.sysdevices

See Also