My developer was looking for all stored procedures, I just used following simple query to find the results…
select Name from sysobjects where type = 'p'
but it included few non-user developed stored procedures
I had to do some research to eliminate ms shipped and microsoft_database_tools_support type procedures. Following is the updated query for quick reference.
select SCHEMA_NAME(sao.schema_id) + '.' + sao.name as StoredProcedure_Name from sys.all_objects sao (nolock) left join sys.extended_properties ep (nolock) on ep.major_id = sao.object_id where sao.type = 'p' and sao.is_ms_shipped = 0 and isnull(ep.name,'') <> 'microsoft_database_tools_support'
Note: exec sp_stored_procedures lists all the procedures available on the database