Listing Stored Procedures From Every Database

Recently I answered a question on AskSSC that I thought I would create a quick blog about.  Someone had asked if there was an easy way to run SELECT * FROM SYS.PROCEDURES on every database.  I immediately thought of the undocumented stored procedure sp_msforeachdb.  I posted a response and gave a bit of advise to modify the query to make it an insert statement and to only return the columns from SYS.PROCEDURES that the OP needed.  Another answer was to use PowerShell which is also an excellent way of achieving the results.  To expand my answer with using sp_msforeachdb I have the following process to create a table and insert the results there.  Keep in mind that I have a local database called “Tim” on my instance as a dumping ground for testing items.  Change “Tim” to what ever database name you would prefer.

USE Tim
GO

CREATE TABLE [dbo].[PROCEDURES]
(
[DB_NAME] [nvarchar](128) NULL
,[name] [sysname] NOT NULL
,[OBJECT_ID] [int] NOT NULL
,[SCHEMA_ID] [int] NOT NULL
,[TYPE] [char](2) NOT NULL
,[type_desc] [nvarchar](60) NULL
,[create_date] [datetime] NOT NULL
,[modify_date] [datetime] NOT NULL
)
ON  [PRIMARY]

EXEC sp_msforeachdb ‘USE ?; INSERT INTO TIM.DBO.PROCEDURES
(DB_NAME, NAME, OBJECT_ID, SCHEMA_ID, TYPE, TYPE_DESC, CREATE_DATE,MODIFY_DATE)
SELECT DB_NAME() as DB_NAME,NAME, OBJECT_ID, SCHEMA_ID, TYPE, TYPE_DESC, CREATE_DATE, MODIFY_DATE
FROM  SYS.PROCEDURES’

SELECT  DB_NAME
,       NAME
,       OBJECT_ID
,       SCHEMA_ID
,       TYPE
,       TYPE_DESC
,       CREATE_DATE
,       MODIFY_DATE
FROM    TIM.DBO.PROCEDURES