T-SQL Reference - System Stored Procedures

System Stored Procedures

This section provides reference information for the Transact-SQL (T-SQL) system stored procedures that are available in SQL Server.

Overview

System stored procedures are built-in stored procedures provided by SQL Server that perform various administrative and maintenance tasks. They are identified by the sp_ prefix.

Categories of System Stored Procedures

System stored procedures can be broadly categorized as follows:

Configuration and Management: Procedures for setting server options, managing logins, and monitoring server activity. (e.g., sp_configure, sp_who2)
Database Maintenance: Procedures for tasks like backing up, restoring, and repairing databases. (e.g., sp_backup_database, sp_restore_database)
Replication: Procedures related to setting up and managing SQL Server replication.
Security: Procedures for managing permissions, roles, and user accounts. (e.g., sp_grantdbaccess, sp_revokedbaccess)
Internal and System: Procedures used by SQL Server itself for internal operations. (Use with caution!)

Commonly Used System Stored Procedures

`sp_configure`

Allows you to view and change server-level configuration options.

-- View all configuration options
EXEC sp_configure;

-- Change the 'max server memory (MB)' option
EXEC sp_configure 'max server memory (MB)', 2048;
RECONFIGURE;
GO

`sp_who2`

Provides information about the current users and processes on an instance of SQL Server.

EXEC sp_who2;

-- Get information about a specific SPID (Server Process ID)
EXEC sp_who2 55;

`sp_help`

Returns information about a database object, such as a table, view, or stored procedure.

EXEC sp_help 'YourTableName';

`sp_databases`

Lists all databases on the SQL Server instance.

EXEC sp_databases;

Finding Specific System Stored Procedures

You can query the sys.procedures catalog view to list all stored procedures, including system stored procedures.

SELECT name
FROM sys.procedures
WHERE is_ms_shipped = 1
ORDER BY name;

For detailed syntax, parameters, and usage examples for each system stored procedure, please refer to the specific documentation for your version of SQL Server.

Important Considerations

System stored procedures are part of the SQL Server system. Modifying them or using them incorrectly can lead to unintended consequences or system instability.
Always test changes in a non-production environment before implementing them on a live system.
Consult the official Microsoft documentation for the most accurate and up-to-date information.