How to get a list of all SPs in all user databases?

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

Try something like this...

CREATE TABLE #procList (procCatalog VARCHAR(256), procSchema VARCHAR(256), procName VARCHAR(256))

INSERT INTO #procList

EXEC sp_MSforeachdb

'SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME

FROM ?.information_schema.routines

WHERE routine_type = ''PROCEDURE'''

SELECT * FROM #procList WHERE procCatalog NOT IN ('msdb','master')

DROP TABLE #procList

SELECT quote FROM brain WHERE original = 1
0 rows returned

Steve Jones - SSC Editor SSC Guru Points: 734842 More actions · Answer 2

SSC Guru

Points: 734842

December 17, 2015 at 1:16 pm

You can query sys.procedures as well.

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 3

This can also help to list procedure

select * from sys.all_objects where type='p' and is_ms_shipped=0

but it does not list system procedures:

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 4

SSChampion

Points: 14047

December 17, 2015 at 9:53 pm

Thank you all.

Regards,
SQLisAwe5oMe.