sp_helpdb (T-SQL)

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)	Database status (such as truncate log on check point).

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

Note 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

ALTER DATABASE	sp_dboption
CREATE DATABASE	sp_renamedb
sp_configure	System Stored Procedures