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