System Stored Procedures

System stored procedures are built-in stored procedures that are installed with SQL Server. They perform a variety of system-level tasks, such as managing database objects, monitoring server performance, and performing backups and restores. You can execute system stored procedures by prefixing their names with sp_. Most system stored procedures are stored in the master database.

Overview

System stored procedures provide a powerful way to interact with and manage your SQL Server instance. They are essential for administrators and developers alike. Here are some common categories:

Database Management: Procedures for creating, altering, and dropping databases and their objects.
Security Management: Procedures for managing logins, users, permissions, and roles.
Performance Monitoring: Procedures to retrieve information about server activity, query execution, and resource usage.
Backup and Restore: Procedures to initiate and manage database backups and restores.
Configuration: Procedures to view and modify server configuration settings.

Common System Stored Procedures

Database Management Procedures

These procedures help in managing the structure and contents of your databases.

sp_databases: Lists all databases on the server.
sp_helpdb: Provides information about a specific database.
sp_addlinkedserver: Adds a linked server.
sp_tables: Lists tables and views in a database.

Security Management Procedures

Control access and permissions within your SQL Server instance.

sp_addlogin: Adds a new login to the server.
sp_adduser: Adds a new user to a database.
sp_grantdbaccess: Grants database access to a login.
sp_revokedbaccess: Revokes database access from a login.

Performance Monitoring Procedures

Gain insights into your SQL Server's performance and resource utilization.

sp_who: Shows information about current users and processes.
sp_monitor: Displays server activity statistics.
sp_lock: Shows information about locks held by processes.

Example: Checking Current Processes

To see all active processes and their status, you can execute:

EXEC sp_who;

Backup and Restore Procedures

Essential for data protection and disaster recovery.

sp_addumpdevice: Creates a backup device.
BACKUP DATABASE (This is a T-SQL statement, not a stored procedure, but often used with related system procedures).
RESTORE DATABASE (Similar to BACKUP, a T-SQL statement).

Tip

While BACKUP DATABASE and RESTORE DATABASE are T-SQL statements, they are the primary tools for data protection. You can use system stored procedures like sp_addumpdevice to configure where these backups are stored.

Configuration Procedures

Modify and view server-level configuration options.

sp_configure: Displays and modifies server configuration options.
sp_helpsort: Shows available sort orders.

Note

When using sp_configure, you often need to run RECONFIGURE WITH OVERRIDE to apply the changes. Be cautious when changing server configuration settings, as they can significantly impact performance and stability.

Important Considerations

Permissions: Executing system stored procedures requires appropriate permissions. Many require membership in the sysadmin fixed server role.
master Database: Most system stored procedures reside in the master database. You can execute them from any database context by prefixing them with master.dbo., e.g., EXEC master.dbo.sp_databases;.
Changes Across Versions: While core system stored procedures remain consistent, some may be deprecated or new ones introduced in different versions of SQL Server. Always refer to the documentation for your specific version.

Warning

Avoid executing system stored procedures directly on production systems unless you are fully aware of their impact. Always test changes in a non-production environment first.