Printed 2015/11/29 04:47PM

SQL Server – sp_help without arguments.

By Vishal.Gajjar, 2011/08/24

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)


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

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 (

Filed under: SQLServer
Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.