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.
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.
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.
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:
- SQL SERVER – System Stored Procedures I Use to Get Started
- SQL SERVER – System Stored Procedure sys.sp_tables
- SQL SERVER – System procedures to know SQL Server Version
- SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012
- SQL SERVER – System Procedure to List Out Table From Linked Server
- SQL SERVER – Stored Procedures Advantages and Best Advantage
- SQL SERVER – List All Frequently Ran Stored Procedure From Server Cache
Reference: Pinal Dave (http://blog.SQLAuthority.com)
