Top 5 SQL System stored procedures every developer should know

There are a number of documented and undocumented system stored procedures in MS SQL Server that are helpful for every web developer working with databases. From the developer’s perspective, here’s a list of 5 System stored procedures that are my favorite.



1. sp_help



Purpose



sp_help gives information about database objects. If you wanted to quickly know the structure of a table but are too lazy to look for the schema diagram or to dig for the table you are interested in within the Object explorer, sp_help is here to help



Syntax



It can be used without parameters to get the information of objects in the database.



sp_help



It can be used with a parameter to get the information of a particular object



sp_help



Example



sp_help 'dbo.tblCompany'






2. sp_helptext



Purpose



sp_help gives definition information of objects such as system stored procedures, user defined stored procedures, user defined functions, triggers etc.



Syntax



sp_helptext



Example



sp_helptext 'dbo.sp_tblCompany'




3. sp_MSforeachtable



Purpose



This is a very useful stored procedure for executing a command for ALL the tables in the database. Say you wanted to get the number of rows in all the tables in your database, you could write



Example



EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'



The literal ? is used as a token to replace the table name.



4. sp_depends



Purpose



Ever wanted to make a change to a table but were not sure what other objects are dependent on this table? There could be views or stored procedures that could break due to this change. In situations like this, sp_depends come to the rescue.



Syntax

sp_depends



Example



sp_depends 'dbo.tblCompany'




5. sp_spaceused



Purpose



This is a simple stored procedure that gives information on the size of the database or the database objects



Syntax



If it is used without parameters, it would return the database information



sp_spaceused



If it is used with a parameter, it would return the information on the object



sp_spaceused



Example



sp_spaceused 'dbo.tblCompany'