sp_helpdb (Transact-SQL)

sp_helpdb (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

sp_helpdb (Transact-SQL)

Reports information about a specified database or all databases.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_helpdb [ [ @dbname= ] 'name' ]

Arguments

[ @dbname= ] 'name': Is the name of the database for which information is reported. name is sysname, with no default. If name is not specified, sp_helpdb reports on all databases in the sys.databases catalog view.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
name	sysname	Database name.
db_size	nvarchar(13)	Total size of the database.
owner	sysname	Database owner, such as sa.
dbid	smallint	Database ID.
created	nvarchar(11)	Date the database was created.
status	nvarchar(600)	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. Non-Boolean options are listed with their corresponding values in the form of option_name=value. For more information, see ALTER DATABASE (Transact-SQL).
compatibility_level	tinyint	Database compatibility level: 60, 65, 70, 80, or 90.

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	File ID.
filename	nchar(260)	Operating-system file name (physical file name).
filegroup	nvarchar(128)	Filegroup in which the file belongs. NULL = file is a log file. This is never a part of a filegroup.
size	nvarchar(18)	File size in megabytes.
maxsize	nvarchar(18)	Maximum size to which the file can grow. A value of UNLIMITED in this field indicates 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 a data file, the value is 'data only' and for the log file the value is 'log only'.

Remarks

The status column in the result set reports which options have been set to ON in the database. All database options are not reported by the status column. To see a complete list of the current database option settings, use the sys.databases catalog view.

Permissions

When a single database is specified, membership in the public role in the database is required. When no database is specified, membership in the public role in the master database is required.

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

Examples

A. Returning information about a single database

The following example displays information about the AdventureWorks database.

EXEC sp_helpdb N'AdventureWorks';

B. Returning information about all databases

This following example displays information about all databases on the server running SQL Server.

EXEC sp_helpdb;
GO

Reference

Database Engine Stored Procedures (Transact-SQL)
ALTER DATABASE (Transact-SQL)
CREATE DATABASE (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance