sp_help returns information about database objects and types.

@object_name argument is optional for sp_help. When executed without any argument it returns summary information for objects of all types in the database. These includes all user tables, system tables, stored procedures, functions, extended stored procedure, Assembly Stored Procedures, etc…

USE [master]

EXEC sp_help

Partial Result Set:

Name                                Owner             Object_type

———————               ————-     ——————-

MSreplication_options               dbo               user table

…

sp_MScleanupmergepublisher          dbo               stored procedure

…

EventNotificationErrorsQueue        dbo               queue

…

queue_messages_1003150619           dbo               internal table

…

DF__spt_value__statu__4460231C      dbo               default (maybe cns)

…

CHECK_CONSTRAINTS                   INFORMATION_SCHEMA view

…

all_columns                         sys               view

…

fn_EnumCurrentPrincipals            sys               table function

…

sysallocunits                       sys               system table

…

fn_cColvEntries_80                  sys               scalar function

…

dm_cryptographic_provider_algorithms  sys             inline function

…

sp_AddFunctionalUnitToComponent     sys               extended stored proc

…

fn_cdc_decrement_lsn                sys               assembly scalar function

…

ORMask                              sys               aggregate function

…

sp_FuzzyLookupTableMaint
enanceInstall                       sys               assembly stored procedure

…

This is documented in BOL, but there is a slim chance that you have gone through BOL for sp_help!

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

Filed under: SQLServer