Run a query against each database | sp_MSforeachdb

Incidentally today I had responded to 3 posts in different SQL Server boards, where the requirement was to run a query against every database in an instance. In such a case, the undocumented stored procedure sp_MSforeachdb is of great use. In the below example, I am searching for a stored procedure which begins with xyz. [?] in the query denominates the Database Name.

exec sp_MSforeachdb
@command1=
'PRINT [?] SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''xyz%''
AND ROUTINE_TYPE=''PROCEDURE'''

Similarly there is also another undocumented stored procedure called sp_MSforeachtable. Like sp_MSforeachdb, this will execute a query against all the tables in a database. In this case [?] denominates the Table Name.

2 thoughts on “Run a query against each database | sp_MSforeachdb

  1. Pingback: Find Startup procedures in an instance | SQLServerPedia

  2. Pingback: Find SQL Server and Server information using a one line query | SQLServerPedia

Comments are closed.