SQL SERVER – sp_helpdb – Accidental Discovery

If you are a regular reader of this blog post, you probably are aware that primary business is to help people improve their SQL Server query performance while consulting service Comprehensive Database Performance Health Check. Recently while working with a client, I used a system stored procedure sp_helpdb and they were surprised that even something like this exists.

SQL SERVER - sp_helpdb - Accidental Discovery sp-helpdb-800x573

sp_helpdb

This is one of the very old systems stored procedure that returns information about either all of your database or your system database. Let us see how it works.

First, run the following stored procedure without any parameters.

EXEC sp_helpdb

Upon running the stored procedure, you will get the following output.

SQL SERVER - sp_helpdb - Accidental Discovery sp_helpdb1

The output contains information related to all the database which are available on your instance.

Now select any of the databases and run the same stored procedure with an additional parameter – the name of the database.

EXEC sp_helpdb AdventureWorks2017

When you run the above script it gives us additional information about all the files and related information.

SQL SERVER - sp_helpdb - Accidental Discovery sp_helpdb2

The stored procedure was always available but I have seen very little adoption of it. There are so many different ways to get the same information in SQL Server that it is difficult to remember all the options. As long as you are familiar with at least one method and it does the job for you, we should be alright.

Here are a few additional related blog posts:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Previous Post
SQL SERVER – Blocking Tree – Identifying Blocking Chain Using SQL Scripts
Next Post
SQL SERVER – SSMS – SET ROWCOUNT – Real-World Story

Related Posts

No results found.

Leave a Reply