Referencing a stored procedure by a name description across all databases in a SQL Server 2000 Instance

Question

Does anyone have a valid method in referencing system information to search for a stored procedure across all databases in a SQL Server 2000 instance? I am currently tasked with migrating crystal reports to SSRS and the customer has roughly 500 or more crystal reports with stored procedures spread over multiple databases. I know I can filter each database's programmability folder by text but I was looking for an outcome in which I can query master.dbo.sysdatabases and reference that to syscomments in which I can specify text and find a stored procedure tied to a database.

Answer 1

Just use a cursor to loop through the databases and query the sysobjects table:

Select * from sysobjects 
where type='P' 
and name='StoredProcName'

(Cursor code not included.) Or use the (undocumented) sp_MSforeachdb:

EXEC sp_MSforeachdb 'USE ? SELECT ''?'' as dbname, name FROM sysobjects 
      WHERE type = ''P'' AND name = ''StoredProcName''

(don't split that over two lines, though)

Also, sp_MSforeachdb has known issues, like failing on very long database names, so I usually prefer the cursor.

Answer 2

If anyone is curious how the cursor code would look, from this MSDN post:

DECLARE @SearchString VARCHAR(255)
DECLARE @name VARCHAR(50)
DECLARE @db_name1 VARCHAR(50)
DECLARE @db_name2 VARCHAR(50)
DECLARE @sql VARCHAR(100)
SET @SearchString = 'a_ras_Arlenes_ship_sched_sp'

DECLARE db_cursor CURSOR FOR 

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor 

FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name  

While @@FETCH_STATUS = 0
BEGIN
  SET @db_name1 =@name+'.dbo.syscomments'
  SET @db_name2 =@name+'.dbo.sysobjects'    

  SELECT @sql = 'SELECT DISTINCT b.name FROM ' + @db_name1 
              + ' a JOIN ' + @db_name2 + ' b ON a.id = b.id'
              +' WHERE a.text like %' + @SearchString + '% ORDER BY b.name'

  EXEC(@sql)

  FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name

END 
Close db_cursor
DEALLOCATE db_cursor