Useful SQL Server System Stored Procedures You Should Know

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):

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
sp_depends	Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported

These were some System Stored Procedures in SQL Server that come in very handy. If you have been using any other system stored procedures (not listed here) on a frequent basis, feel free to share them via the comments section.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook