Some very useful System Stored Procedure in SQL Server

In this post we would be going through some of the very useful system defined Stored Procedures in SQL Server.
The inspiration behind writing this post is the deep urge to share the knowledge and benefit I have got after using these stored procedures. Few years back I was asked in an interview that what is the size of the database I was using. Having only worked on writing queries and SP etc... as a developer I never thought about what could be the size of the database we were using or What is the amount of space which the objects in our DB has consumed ?. Thankfully the answer to all such questions and many more could be easily found by executing some of these system defined Stored Procedures.

Let us go through each one of them and try executing these on your SQL Server to discover amazing stats of your database.
Note : These procedures does take few parameters but I will discuss only the commonly used parameters and the actual list of parameters can be referred from the official documentation for the readers to try out executing these SPs by themselves. The examples of the database and table which I would use is from AdventureWorks2019 database.

1. sp_databases

This SP lists down the databases that resides in the instance of the SQL Server. It also returns the size of the databases in kilobytes present on the SQL Server.

exec sp_databases

2. sp_spaceused

This SP returns the total number of rows ,disk space reserved and the disk spaced in KB used by a table or view in the current database if the table or view name is specified in the parameter or otherwise it would return the disk space reserved and the space used by the database in MB.

exec sp_spaceused
exec sp_spaceused 'Person.Address'

3. sp_tables

This SP returns the list of tables and views that could be queried in the current environment. This would again return the complete list if it is executed without parameters or fetch the list matching the input parameters.

exec sp_tables
exec sp_tables '%Address%' -- Using wildcard to fetch the list of objects matching with Address

4. sp_columns

This returns the list of columns present in the table. This SP has the table name as the required parameter and few other optional parameters to filter out the result.

exec sp_columns '%Address'

The above statement lists down all the columns present in all the tables ending with the text 'Address'.

5. sp_depends

This SP displays the object level dependencies in the database. This returns the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure.
This SP requires the object names as the input parameter to list the dependencies. The object name could be a table, procedure, view, user defined function or a trigger.

exec sp_depends 'Person.Address'
exec sp_depends 'HumanResources.uspUpdateEmployeeHireInfo'

6. sp_executesql

This SP executes a dynamically generated sql statement or it can also be used to reuse a sql statement a number of times.

declare @statement nvarchar(max) = 'select top 10 * from Person.Address where AddressId = @addressId'
exec sp_executesql  @statement,  N'@addressId int',@addressId = 10

7. sp_help

This SP lists the information of database objects i.e table, view, procedure, triggers, user defined data type or a data type.

--The below statement gives the information of the Person.Address table
exec sp_help 'Person.Address'

--The below statement gives the information of all the objects of the database.
exec sp_help

8. sp_helpconstraint

This lists down all the constraints of the tables which is passed in the input parameter.

exec sp_helpconstraint 'Person.Address'

9. sp_helpdb

This displays the information of the specified database or the entire database. Unlike the procedure sp_databases , this procedure reports additional information of the database in addition to the size, like owner, dbid , created date, status and compatibility level. Another difference is that this sp reports the size in MB while the other one reports the size in KB.

exec sp_helpdb 
exec sp_helpdb 'AdventureWorks2019'

10. sp_helptext

This SP is most frequently used by developers and this displays the definition of the procedure , function , triggers and views. This also displays the definition of the system stored procedures.

--This displays the definiton of the system stored procedure. Here the system procedure being itself.
exec sp_helptext 'sp_helptext'
exec sp_helptext 'HumanResources.uspUpdateEmployeeHireInfo'

These are some of the system stored procedures which I find useful in the day to day development activity however there is a huge list of system stored procedures which is very useful to the developers as well as system administrators for the database security and maintenance activities.

Please feel free to add the procedures in the comments below which I might not have covered above.