By: Eric Blinn
Overview
In this section we will cover questions like: what stored procedures are in the master database, can I see the code, can I change the stored procedures in the master database.
What are the key stored procedures in the SQL Server master database that are important for SQL Server Professionals to know about?
This is another situation where an entire book could be dedicated to these procedures. Here is a sampling of some of the more popular procedures.
The procedure sp_help will return information about another object that it accepts as a parameter. For instance, calling sp_help with another stored procedure name as the parameter will return the parameter list of the target procedure. Calling it with a table name as the parameter will show the column list, index list and a few other data elements for that table. Here is a sample where it is asking for the parameters available to itself.
exec sys.sp_help sp_help
The procedure sp_helptext accepts an object name as a parameter and will return the text of the object as a create object script. Here is it asking for the text of sp_help.
exec sys.sp_helptext sp_help
The helpindex procedure accepts a table name as a parameter and provides details of the rowstore and columnstore indexes for the target table. In this example is going to return index details for a table called orders.
exec sys.sp_helpindex orders
This procedure adds a user to a role. It is commonly called using the SSMS GUI, but can be called directly. This execution would add the user "ERIC" to the db_owner role.
exec sys.sp_addrolemember 'db_owner', 'ERIC'
This procedure presents a list of linked servers on the instance.
exec sys.sp_linkedservers
This procedure is used to view or change systemwide settings. If called without parameters it lists all available settings. If called with only a setting name it displays that setting. If called with a setting name and a new value then the setting is changed to that value. This execution below would change the max server memory setting to 28GB.
exec sys.sp_configure 'max server memory (MB)', 28672
This procedure gives a snapshot of everyone who is logged in to the instance and includes blocking information.
exec sys.sp_who2
Can I see the code for the objects in the SQL Server master database and if so, how?
These stored procedures are mostly TSQL based and can be viewed by either scripting them in SSMS or using sp_helptext. In the SSMS Object Explorer, the procedures will be buried pretty deep in the tree under the database, Programmability, Stored Procedures, and, finally, System Stored Procedures.
Just like any other procedure, right clicking will open a context menu with the Modify option.
exec [sys].[sp_helptext] sp_helptext
Yes, sp_helptext can be used to view the procedure text of sp_heptext. This screenshot shows what happens when the query is executed.
Can I change the objects in the SQL Server master database?
Remember that most of these objects are actually stored in the read-only database mssqlsystemresource and therefore cannot be changed. Attempting to change them will result in an error as seen below.
Can I store my own objects in the SQL Server master database?
SQL Server will allow objects to be added to the master database, but it is better to not add stored procedures and keep the database as clean as possible.
