Post reply

Find sp in server

  • April 16, 2009 at 10:03 pm

    #132832

    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

    #979430

    Hi,

    Try this,

    SELECT * FROM DatabaseName..SYSComments Where text = 'sp_FindDetails'

  • April 17, 2009 at 6:38 am

    #979589

    Try this

    Exec sp_MSForEachDB 'SELECT ''?'' AS DataBaseName, * FROM ?..sysObjects WHERE name = ''sp_FindDetails'''

  • April 24, 2009 at 8:08 am

    #983559

    I ran across this Object Finder add-in for SSMS some time ago. It seems works well.

    Hope it helps. Regards, Grady Christie

  • April 24, 2009 at 2:47 pm

    #983838

    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