SQL System Stored Procedures
System stored procedures are built-in stored procedures that perform various administrative, maintenance, and information-retrieval tasks within SQL Server. They are executed using the sp_ prefix.
Categories of System Stored Procedures
System stored procedures can be broadly categorized based on their functionality:
1. Information Retrieval Procedures
These procedures provide information about the SQL Server instance, databases, objects, and configurations.
sp_helpdb: Displays information about the current database.sp_tables: Lists tables in the current database.sp_columns: Lists columns for a specified table.sp_helpindex: Displays information about indexes on a table.sp_lock: Shows information about locks held on resources.sp_who: Lists currently active users and processes.
2. Maintenance Procedures
These procedures assist in performing regular maintenance tasks for SQL Server.
sp_updatestats: Updates statistics for all user tables in the database.sp_recompile: Recompiles stored procedures, triggers, and views.sp_monitor: Displays SQL Server activity information.
3. Configuration Procedures
These procedures allow modification of SQL Server configuration options.
sp_configure: Allows you to view and change server configuration options.sp_addserver: Adds a local or remote server.
4. Security Procedures
These procedures manage logins, users, permissions, and roles.
sp_addlogin: Creates a new SQL Server login.sp_adduser: Creates a new database user.sp_grantdbaccess: Grants access to a database user.sp_denydbaccess: Denies access to a database user.
Commonly Used System Stored Procedures
sp_helpdb
Retrieves information about databases, including name, owner, creation date, and size.
EXEC sp_helpdb;To get information about a specific database:
EXEC sp_helpdb 'AdventureWorks2019';sp_tables
Lists tables and views in the current database.
EXEC sp_tables;You can filter by table type:
EXEC sp_tables @table_type = "'TABLE'";sp_columns
Returns information about columns for a specified table.
EXEC sp_columns 'Production.Product';Important Note
While system stored procedures are powerful, exercise caution when using them, especially those that modify server configurations or security settings. Always refer to the official SQL Server documentation for detailed syntax, parameters, and potential side effects.
Executing System Stored Procedures
System stored procedures are executed using the EXEC or EXECUTE statement, typically prefixed with sp_. For example:
EXEC sp_who2;Caution
Avoid creating user-defined stored procedures with names that start with sp_ to prevent conflicts with future SQL Server system stored procedures.