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.
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)
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.
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