Can I query stored procs in all databases with one query without dynamic sql

Question

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 
     'select ' + '''' + name + '''' + ', name from [' + name 
   + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
FROM sys.databases 
-- I get a collation error from the following
WHERE name NOT IN ('ReportServer', 'ReportServerTempDb');

Can I do the same thing without dynamic sql and without the cursor?

Answer 1

You can do it without the cursor (or without a cursor-like operation). For example you can do it slightly differently like this:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'select ''' + db.name + ''', p.name 
        from ' + QUOTENAME(db.name) + N'.sys.procedures AS p 
        WHERE p.name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

You can also avoid multiple result sets going back by doing this:

CREATE TABLE #x(d SYSNAME, n SYSNAME);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'insert #x select ''' + db.name + ''', p.name 
        FROM ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

SELECT d, n FROM #x ORDER BY d, n;

If you're really only after a single, specific stored procedure though, see Martin's answer.

Answer 2

You can use the undocumented stored procedure sp_msforeachdb.

Here's one article for you: SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb by Gregory A. Larsen.

But you should also read:

• Bad habits to kick : relying on undocumented behavior by Aaron Bertrand.

Answer 3

Can I do the same thing without dynamic sql and without the cursor?

Yes. As long as the procedure is known to be in the same schema in all databases. For example to find all databases containing a procedure called dbo.usp_MyProc you can use

SELECT name
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name) + '.dbo.usp_MyProc', 'P') IS NOT NULL