It is sometimes necessary to have information regarding the database objects like stored procedures and triggers for e.g. we need to get information about a stored procedure like what paramters it has and so on.
The sp_help system stored procedure comes in handy.

What this store procedure does is that it returns result set. The result will be many times more than one in number for e.g If we need to get some information about user stored procedure it will return two resultsets. This result sets can then be captured in the front end using either ADO or my favourite ADo.net and used for the intended purpose.