System Stored Procedures

System stored procedures are Transact-SQL stored procedures that are pre-installed in SQL Server. They are used to perform various administrative and maintenance tasks.

Categories of System Stored Procedures

System stored procedures are categorized by their functionality. Here are some common categories:

Configuration Procedures

Used to configure SQL Server settings.

sp_configure
sp_settings

Security Procedures

Used to manage logins, users, permissions, and roles.

sp_addlogin
sp_adduser
sp_grantdbaccess
sp_addrolemember

Maintenance Procedures

Used for database maintenance, backup, and restore operations.

sp_helpdb
sp_databases
sp_backupmediafamily
sp_dbcmptlevel

Information Procedures

Used to retrieve information about SQL Server objects, databases, and system status.

sp_help
sp_tables
sp_columns
sp_who
sp_monitor

Replication Procedures

Used to manage and configure replication.

sp_adddistributor
sp_addpublication

SQL Mail Procedures

Used for sending email notifications.

sp_send_dbmail

Commonly Used System Stored Procedures

Here are some of the most frequently used system stored procedures:

sp_help 'object_name': Returns information about a database object such as a table, view, or stored procedure.
sp_configure 'option_name': Displays the values of configuration options or enables/disables a configuration option.
sp_who: Returns information about current users and processes on the SQL Server instance.
sp_adduser 'login_name', 'user_name': Adds a new database user to the current database.
sp_tables: Returns a list of tables in the current database.
sp_columns 'table_name': Returns information about the columns in a specified table.

Permissions

Executing system stored procedures requires specific permissions. Some procedures can be executed by any user, while others require membership in fixed server roles such as sysadmin or db_owner.

System Stored Procedures vs. User-Defined Stored Procedures

System stored procedures are pre-installed by SQL Server and perform system-level tasks. User-defined stored procedures are created by users to encapsulate custom business logic or repetitive tasks within the database.