Tip: Use T-SQL to View Database Information

You can use Transact-SQL (T-SQL) to examine database information. In SQL Server Management Studio, access the Query view. You can do this by right-clicking the name of a server to which you have already connected in the Object Explorer view and then selecting New Query. Alternatively, you can click New Query on the main toolbar, select Database Engine Query, and then establish a connection to the Database Engine on a specific server.

After you have accessed the Query view, use the following command, where dbname is the name of the database you want to examine:
sp_helpdb <dbname>
go

When you view database information in this way, you get an overview of the database as well as a listing of current data and log files. Here is a summary of the information available when you view database properties using T-SQL. This data is returned in two different result sets; you will need to scroll down in the Results pane to see the additional result set.

compatibility_level The current compatibility level of the database. The level 90 indicates SQL Server 2008 compatibility.
created The date the database was created.
db_size The total size of the database, including all data and log files.
dbid The unique identifier for the database on the current server.
filegroup The filegroup associated with the database file. Filegroups allow you to group sets of database files together.
fileid The unique identifier for the file in the current database.
filename The full file name and path.
growth The number of megabytes or percent by which the file grows.
maxsize The maximum file size. Unlimited means there is no limit.
name The name of the database or file (without a file extension).
owner The database owner.
size The current size of a file.
status The database status.
usage The way the file is used, such as data only or log only.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.