How to Find Which Database Contains a Stored Procedure in SQL Server

If you manage a SQL Server instance with dozens of databases, you've probably run into this: you know a stored procedure exists somewhere, but you have no idea which database it lives in. Opening each database in SSMS and browsing the Programmability folder one by one isn't realistic when you have 40 of them.

The good news is that you can search every database on the instance with a single query.

The Solution: Dynamic SQL over sys.databases

The idea is simple. sys.databases lists every database on the instance. For each one, we build a query against that database's sys.procedures catalog view, glue them all together with UNION ALL, and run the result once.

DECLARE @ProcName sysname = N'YourProcedureName'; -- the procedure you're looking for DECLARE @sql nvarchar(max) = N''; SELECT @sql = @sql + N' SELECT ' + QUOTENAME(name, '''') + N' AS DatabaseName, s.name COLLATE DATABASE_DEFAULT AS SchemaName, p.name COLLATE DATABASE_DEFAULT AS ProcedureName, p.create_date, p.modify_date FROM ' + QUOTENAME(name) + N'.sys.procedures p JOIN ' + QUOTENAME(name) + N'.sys.schemas s ON s.schema_id = p.schema_id WHERE p.name LIKE @ProcName UNION ALL' FROM sys.databases WHERE state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1; -- Remove the trailing UNION ALL SET @sql = LEFT(@sql, LEN(@sql) - LEN('UNION ALL')); EXEC sp_executesql @sql, N'@ProcName sysname', @ProcName = @ProcName;

The output is a single result set listing every database that contains a matching procedure, along with its schema and its creation and last-modified dates.

How It Works

  • sys.databases gives us the list of databases to search.
  • QUOTENAME wraps each database name in brackets, so names with spaces or special characters don't break the query. Used with '''', it also produces a safely quoted string literal for the DatabaseName column.
  • state_desc = 'ONLINE' skips databases that are offline, restoring, or otherwise unavailable.
  • HAS_DBACCESS(name) = 1 skips databases you don't have permission to read, so the whole query doesn't fail halfway through.
  • COLLATE DATABASE_DEFAULT forces the name columns from every database into the same collation (see below).
  • sp_executesql with a parameter passes the procedure name safely instead of concatenating it into the string, which avoids SQL injection and quoting issues.

Why COLLATE DATABASE_DEFAULT?

Databases on the same instance don't always share a collation. If one database uses SQL_Latin1_General_CP1_CI_AS and another uses SQL_Latin1_General_CP1254_CI_AS, combining their name columns with UNION ALL fails with:

Msg 451, Level 16, State 1 Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1254_CI_AS" in UNION ALL operator occurring in SELECT statement column 2.

Adding COLLATE DATABASE_DEFAULT to the text columns converts them all to the collation of the database you're running the script from, so the union works no matter how mixed your instance is.

Tips

Not sure of the exact name? Use wildcards

Because the filter uses LIKE, you can search for partial names:

DECLARE @ProcName sysname = N'%customer%';

This returns every procedure whose name contains "customer", in every database.

Searching inside the procedure body

Sometimes you don't know the procedure name at all, but you know it references a particular table or contains a specific piece of logic. In that case, join to sys.sql_modules and search the definition column instead:

DECLARE @SearchText nvarchar(200) = N'%OrderDetails%'; DECLARE @sql nvarchar(max) = N''; SELECT @sql = @sql + N' SELECT ' + QUOTENAME(name, '''') + N' AS DatabaseName, s.name COLLATE DATABASE_DEFAULT AS SchemaName, p.name COLLATE DATABASE_DEFAULT AS ProcedureName FROM ' + QUOTENAME(name) + N'.sys.procedures p JOIN ' + QUOTENAME(name) + N'.sys.schemas s ON s.schema_id = p.schema_id JOIN ' + QUOTENAME(name) + N'.sys.sql_modules m ON m.object_id = p.object_id WHERE m.definition LIKE @SearchText UNION ALL' FROM sys.databases WHERE state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1; SET @sql = LEFT(@sql, LEN(@sql) - LEN('UNION ALL')); EXEC sp_executesql @sql, N'@SearchText nvarchar(200)', @SearchText = @SearchText;

Note that encrypted procedures (WITH ENCRYPTION) have a NULL definition, so they won't show up in this search.

What About sp_MSforeachdb?

You'll often see sp_MSforeachdb recommended for this kind of task. It works, but it has a couple of drawbacks:

  • It's undocumented and unsupported by Microsoft, so its behavior isn't guaranteed.
  • It has a known history of silently skipping databases under certain conditions, which is exactly what you don't want when you're trying to find something.

Building the query yourself from sys.databases, as shown above, is more predictable and gives you full control over which databases are included.

Wrapping Up

With one query, you can locate a stored procedure across every database on your instance in seconds, whether you know its exact name, only part of it, or just something it contains. It's worth saving this script as a snippet; it comes in handy more often than you'd expect.