Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Radney - Database Professional

Tim Radney - Database Professional by Tim Radney I am a Sr DBA for a top 40 US bank. I live in the south eastern US. I have been working with database since 1999 but only full time for the past three years.

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


Comments

Posted by Anonymous on 5 July 2011

Pingback from  Dew Drop – July 5, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.