I need to look for a stored proc across many databases in the same Sql Server. I was able to get the needed results using the following:
select ‘select ‘ + char(39) + name + char(39) +
‘ as [DBName], [name] from ‘ + name + ‘.dbo.sysobjects where xtype = ‘
+ char(39) + ‘P’ + char(39) + ‘and name = ‘ + char(39) + ‘{Stored Procedure Name}’
+ char(39) + ‘ UNION ALL’ from sysdatabases
After this is run the results will be as follows:
select ‘master’ as [DBName], [name] from master.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘tempdb’ as [DBName], [name] from tempdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘model’ as [DBName], [name] from model.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘msdb’ as [DBName], [name] from msdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘pubs’ as [DBName], [name] from pubs.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘Northwind’ as [DBName], [name] from Northwind.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
The last UNION ALL must be removed or the follow error will display:
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ‘ALL’.
After removing the UNION ALL for the last select is should look as follows:
select ‘master’ as [DBName], [name] from master.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘tempdb’ as [DBName], [name] from tempdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘model’ as [DBName], [name] from model.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘msdb’ as [DBName], [name] from msdb.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘pubs’ as [DBName], [name] from pubs.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
UNION ALL
select ‘Northwind’ as [DBName], [name] from Northwind.dbo.sysobjects
where xtype = ‘P’and name = ‘{StoredProcName}’
This will return a select statement that when run will list the name of the server as well as the name of the stored proc that is searched for.
Results will be as follows:
DBName name
——— ———-
master {StoredProcName}
model {StoredProcName}