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.