Sql-server – query stored procs in all databases with one query without dynamic sql

sql serverstored-procedures

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?

Best Answer

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.

Related Question