sp_helpdb

Transact-SQL Reference

Transact-SQL Reference

sp_helpdb

Reports information about a specified database or all databases.

Syntax

sp_helpdb [ [ @dbname= ] 'name' ]

Arguments

[@dbname=] 'name'

Is the name of the database for which to provide information. name is sysname, with no default. If name is not specified, sp_helpdb reports on all databases in master.dbo.sysdatabases.

Return Code Values

0 (success) or 1 (failure)

Result Sets
Column name Data type Description
name nvarchar(24) Database name.
db_size nvarchar(13) Total size of the database.
owner nvarchar(24) Database owner (such as sa).
dbid smallint Numeric database ID.
created char(11) Date the database was created.
status varchar(340) Comma-separated list of values of database options that are currently set on the database.

Boolean-valued options are listed only if they are enabled. Nonboolean options are listed with their corresponding values in the form of option_name=value.

See ALTER DATABASE for more information.

compatibility_level tinyint Database compatibility level (60, 65, 70, and 80)

If name is specified, there is an additional result set that shows the file allocation for the specified database.

Column name Data type Description
name nchar(128) Logical file name.
fileid smallint Numeric file identifier.
file name nchar(260) Operating-system file name (physical file name).
filegroup nvarchar(128) Group in 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 filegroup.
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 data file, the usage is data only and for the log file the usage is log only.

Remarks

The status column in the result set reports which bits have been turned on in the status column of sysdatabases. Information from the status2 column of sysdatabases is not reported.

Permissions

Execute permissions default to the public role.

sp_helpdb must access the database(s) on the server to determine the information to be displayed about the database. Therefore, for each database on the server, one of these must apply:

  • The user executing sp_helpdb must have permissions to access the database.

  • The guest user account must exist in the database.

If a database cannot be accessed, sp_helpdb displays error message 15622 and as much information about the database as it can.

Examples
A. Return information about a single database

This example displays information about the pubs database.

exec sp_helpdb pubs
B. Return information about all databases

This example displays information about all databases on the server running Microsoft® SQL Server™.

exec sp_helpdb

See Also

ALTER DATABASE

CREATE DATABASE

sp_configure

sp_dboption

sp_renamedb

System Stored Procedures