question

Are you trying to search by name, or are you trying to search the content of the table/body of the stored procedure?

For a simple "by name" search, it doesn't get much easier than the sp_msforeachdb solution that you posted from SQLServerCentral. If you set aside 20 minutes to properly read it, you'll find that it's a great, and simple solution.

Object references can be found in either the sysobjects table (2000) or sys.objects view (2005+). However, as I'm sure you are aware, these exist separately per database. I was going to suggest using the undocumented sp_msforeachdb stored procedure, but fortunately I checked the article you referenced first.

What about it seems too complicated? Have you tried that method?

SQL Server divides objects into databases for security and other reasons. It is not designed to be easy to reference system views across multiple databases.

If, however, you already know the names of the databases you wish to search (and it's a manageable list), you could try this:

DECLARE @ProcName sysname; SET @ProcName = 'sp_creatediagram'

SELECT 'db1' AS DatabaseName, p.name AS ProcName
FROM db1.sys.procedures p
WHERE p.name = @ProcName
UNION ALL
SELECT 'db2' AS DatabaseName, p.name AS ProcName
FROM db2.sys.procedures p
WHERE p.name = @ProcName

It's not nearly as elegant as the general solution in the article, but it will work for small sets of databases. I think it's about as simple as you're going to get. Just replace the database names in the SELECT and FROM clauses and union as many queries as you have databases to search.

If you want to search for any object, use this instead:

DECLARE @ProcName sysname; SET @ProcName = 'sp_creatediagram'

SELECT 'db1' AS DatabaseName, o.name AS ProcName, o.type_desc
FROM db1.sys.objects o
WHERE o.name = @ProcName
UNION ALL
SELECT 'db2' AS DatabaseName, o.name AS ProcName, o.type_desc
FROM db2.sys.objects o
WHERE o.name = @ProcName

You said TSQL, but I believe that Powershell can help a lot too!

foreach ( $DatabaseName  in Get-ChildItem SQLSERVER:\SQL\$env:computername\default\databases ) {
    set-location  ("SQLSERVER:\SQL\$env:computername\DEFAULT\databases\" + $DatabaseName.name + "\storedprocedures")
    dir | where {$_.name -match "YOURPROCEDURE"} | select $databasename.Name,schema,owner,name,CreateDate,DateLastModified
}

change DEFAULT for your instance if was named

You have the return list NameDatabase,Schema,Owner,Name (stored procedure), Create date and Date Last Modified. We can a lot of other informations, but I think these are the most important.

This code only works for on server/instance, but f you want to multiple servers/instances, its easy to do in PS too !!!! the code will be uniform for 1 or 10000 servers

We use a simple script in-house to find objects with a given string:

DECLARE @Txt VARCHAR(50)
SELECT OBJECT_NAME(id) ObjectName,
        CASE WHEN OBJECTPROPERTY(id, 'IsReplProc') = 1       THEN 'Replication Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsExtendedProc') = 1   THEN 'Extended Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsProcedure') = 1      THEN 'Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsTrigger') = 1        THEN 'Trigger'
             WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1  THEN 'Table-Valued Function'
             WHEN OBJECTPROPERTY(id, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'
             WHEN OBJECTPROPERTY(id, 'IsInlineFunction') = 1 THEN 'Inline function'
        END AS ObjectType
FROM syscomments
WHERE ([TEXT] LIKE @Txt
      )
  AND (OBJECTPROPERTY(id, 'IsProcedure') = 1
    OR OBJECTPROPERTY(id, 'IsReplProc') = 1
    OR OBJECTPROPERTY(id, 'IsExtendedProc') = 1
    OR OBJECTPROPERTY(id, 'IsTrigger') = 1
    OR OBJECTPROPERTY(id, 'IsTableFunction') = 1
    OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
    OR OBJECTPROPERTY(id, 'IsInlineFunction') = 1)
GROUP BY OBJECT_NAME(id), syscomments.id