T-SQL Tutorial

System Stored Procedures


SQL Server System Stored Procedures are predefined procedures that are included in the SQL Server database management system. These procedures are stored in the master database and are used for performing various administrative tasks, such as configuring server settings, managing database objects, and monitoring server performance.

System Stored Procedures are designed to be executed by system administrators or database developers with appropriate permissions. They are also useful for automation tasks, as they can be executed automatically using SQL Server Agent or other scheduling tools. SQL Server database allow you to use various system stored procedures like: sp_tables, sp_table_privileges, sp_stored_procedures, sp_cursor, sp_executesql, sp_rename, sp_lock, sp_help.

Some of the most commonly used System Stored Procedures in SQL Server include:


  • Sp_addextendedproperty - Adds a new extended property to a database object.
  • Sp_autostats - Displays or changes the automatic statistics update option.
  • Sp_columns - Returns column information for the specified database objects.
  • Sp_column_privileges - Returns column privilege information for a table.
  • Sp_special_columns - Returns the optimal set of columns that uniquely identify a row in the table.
  • Sp_configure - Displays or changes global configuration settings for the current server.
  • Sp_databases - Shows all databases name and size for an instance of the SQL Server.
  • Sp_execute - Executes a prepared Transact-SQL statement using a specified handle and optional parameter value.
  • Sp_executesql - Executes a Transact-SQL statement that can be reused many times.
  • Sp_fkeys - Returns foreign key informations.
  • Sp_help - List informations about a database objects.
  • Sp_helpconstraint - Returns a list of all constraint types.
  • Sp_helpdb - List informations about databases.
  • Sp_helpindex - Returns information about the indexes on a table or view.
  • Sp_lock - Shows information about locks.
  • Sp_monitor - Shows statistics about Microsoft SQL Server.
  • Sp_prepare - Prepares a parameterized Transact-SQL statement and returns a statement handle for execution.
  • Sp_pkeys - Returns primary key informations.
  • Sp_rename - Changes the name of a object in the current database.
  • Sp_renamedb - Changes a database name.
  • Sp_tables - Returns a list of objects like tables or views.
  • Sp_helptrigger - Returns the type or types of DML triggers defined on the specified table.
  • Sp_table_privileges - Returns a list of table permissions for the specified table.
  • Sp_server_info - Returns a list of attribute names and matching values for SQL Server.
  • Sp_statistics - Returns a list of all indexes and statistics on a specified table.
  • Sp_stored_procedures - Returns a list of stored procedures.
  • Sp_unprepare - Discards the execution plan created by the sp_prepare stored procedure.
  • Sp_updatestats - Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
  • Sp_who - Returns information about current users, sessions, and processes in an instance of the SQL Server.

System Stored Procedures can be executed using SQL Server Management Studio or any other tool that supports SQL Server. They are an essential tool for managing and maintaining SQL Server databases, and can help simplify and automate common administrative tasks.