System-Defined SQL Stored Procedures

DBMS brands that support stored procedures sometimes provide built-in, systemdefined stored procedures to automate database processing or management functions.
Sybase SQL Server pioneered this use of system stored procedures. Today, hundreds of Transact-SQL system stored procedures provide functions such as managing users, database roles, job execution, distributed servers, replication, and others. Most Transact-SQL system procedures follow this naming convention:

  • SP_ADD_something. Adds a new object (user, server, replica, and so on)
  • SP_DROP_something. Drops an existing object
  • SP_HELP_something. Gets information about an object or objects

For example, the SP_HELPUSER procedure returns information about the valid users of the current database.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *