April 16, 2009 at 10:03 pm
Hi,
Is there a way to identify whether a particular stored procedure is available in what are all database under particaular server.
For Example: sp_FindDetails
Server Name : Records
Database Name: Files
Database name: Data
I want to list out all database name which contain the sp 'sp_FindDetails'
April 17, 2009 at 12:23 am
Hi,
Try this,
SELECT * FROM DatabaseName..SYSComments Where text = 'sp_FindDetails'
April 17, 2009 at 6:38 am
Try this
Exec sp_MSForEachDB 'SELECT ''?'' AS DataBaseName, * FROM ?..sysObjects WHERE name = ''sp_FindDetails'''
April 24, 2009 at 8:08 am
April 24, 2009 at 2:47 pm
Hi
And another solution:
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = (SELECT 'SELECT ' + QUOTENAME(Name, '''') + ' AS DbName, * ' +
'FROM ' + QUOTENAME(Name) + '.sys.procedures ' +
'WHERE name = ' + QUOTENAME('sp_FindDetails', '''') + CHAR(10)
FROM sys.databases
FOR XML PATH(''))
EXECUTE (@sql)
More important
You should avoid to call your procedures "sp_" this causes SQL Server to look first to master-db before searching in your and decreases your performance.
Greets
Flo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply