Post reply

I can search in one database for all sps that have a certain keyword, but how do we repeat this in all database across the server ?

  • October 23, 2018 at 12:13 pm

    #2010836

    Oh hey, i found my own answer.... DONE !

    EXEC sp_MSforeachdb 'SELECT ''?'' + ''.'' + S.name + ''.'' + P.name as Object_Name ,
    CASE
    WHEN charindex(''rx4dclaims'', m.definition, 1 ) > 0 THEN ''rx4dclaims''
    ELSE
    NULL
    END as KEY_WORD
    FROM sys.procedures p
    JOIN sys.sql_modules m ON p.object_id = m.object_id
    JOIN sys.schemas S on ( S.schema_id = p.schema_id )
    where
    charindex(''rx4dclaims'', m.definition, 1 ) > 0
    ORDER BY 1, 2 desc'

  • October 23, 2018 at 12:25 pm

    #2010838

    mw_sql_developer - Tuesday, October 23, 2018 12:13 PM

    Oh hey, i found my own answer.... DONE !

    EXEC sp_MSforeachdb 'SELECT ''?'' + ''.'' + S.name + ''.'' + P.name as Object_Name ,
    CASE
    WHEN charindex(''rx4dclaims'', m.definition, 1 ) > 0 THEN ''rx4dclaims''
    ELSE
    NULL
    END as KEY_WORD
    FROM sys.procedures p
    JOIN sys.sql_modules m ON p.object_id = m.object_id
    JOIN sys.schemas S on ( S.schema_id = p.schema_id )
    where
    charindex(''rx4dclaims'', m.definition, 1 ) > 0
    ORDER BY 1, 2 desc'

    USE THIS INSTEAD ( ONE BELOW ) it works well....  Sorry the one above has a defect. I corrected it below.


    EXEC sp_MSforeachdb 'SELECT ''?'' + ''.'' + S.name + ''.'' + P.name as Object_Name ,
    CASE
    WHEN charindex(''healthplan.com'', m.definition, 1 ) > 0 THEN ''healthplan.com''
    ELSE
    NULL
    END as KEY_WORD
    FROM [?].sys.procedures p
    JOIN [?].sys.sql_modules m ON p.object_id = m.object_id
    JOIN [?].sys.schemas S on ( S.schema_id = p.schema_id )
    where
    charindex(''healthplan.com'', m.definition, 1 ) > 0
    ORDER BY 1, 2 desc'

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply