1

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.

0

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.

2
  • Correct me if I am wrong but I don't think "sp_MSforeachdb" was added until SQL 2005 and Later?
    – tfenwick11
    Apr 24 '17 at 13:31
  • Good question, @tfenwick11, I wasn't sure either. I looked around and found this 2001 article that claims they've been around since SQL 6.0, and this 2004 article that discusses sp_MSforeachdb and its sister procedure (that I wasn't aware of), sp_MSforeachtable. I'd still recommend the cursor.
    – BradC
    Apr 24 '17 at 13:36
1

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
1
  • Looks good. Changed to code formatting.
    – BradC
    Apr 24 '17 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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