Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
Just curious: Why? –  Nick Chammas Mar 3 '12 at 0:22

3 Answers 3

You can use the undocumented stored proc sp_msforeachdb.

Here's one article for you: http://www.databasejournal.com/features/mssql/article.php/3441031

share|improve this answer
    
Wouldn't that be just abstracting the cursor and dynamic sql away into a stored procedure? That would be cleaner though. –  Justin Dearing Mar 2 '12 at 22:21
2  
I strongly recommend against that specific implementation. I'll tell you why here (fifth point): sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/… and here: sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/… –  Aaron Bertrand Mar 2 '12 at 23:04
    
@AaronBertrand thanks for pointing out your alternative to sp_msforeachdb –  Justin Dearing Mar 4 '12 at 16:03
    
Good info, thanks Aaron. Didn't know it was doing / not doing all of that! –  Eric Higgins Mar 5 '12 at 16:49

If you don't want to name the databases explicitly then I don't know that there's any way really to 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 ''' + name + ''', name 
        from [' + name + '].sys.procedures WHERE name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases -- 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 ''' + name + ''', name 
        from [' + name + '].sys.procedures WHERE name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases -- WHERE ...;

EXEC sp_executesql @sql;

SELECT d, n FROM #x ORDER BY d, n;
share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.