System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order):
Read more: SQL Server Curry
System Stored Procedure | Description |
| sp_help | Reports information about a database object, a user-defined data type, or a data type |
| sp_helpdb | Reports information about a specified database or all databases |
| sp_helptext | Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure |
| sp_helpfile | Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server |
| sp_spaceused | Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database |
| sp_who | Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session |
| sp_lock | Reports information about locks. This stored procedure will be removed in a future version of Microsoft SQL Server. Use the sys.dm_tran_locks dynamic management view instead. |
| sp_configure | Displays or changes global configuration settings for the current server |
| sp_tables | Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects. |
| sp_columns | Returns column information for the specified tables or views that can be queried in the current environment |
Read more: SQL Server Curry