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 11:55 am

    #375774

    The following code works fine. It will find the names of all stored procs that use the key word 'rx4dclaims'

    Now then, I have to visit each database and then run this that many times. Isnt there a  cool way to do it once
    HINT: Can someone modify the code listing #2 below ? The code in listing 2 will go across all dbs and find objects that have a certain name. But I am only interested in search across all sp code. 


    SELECT S.name as schemax , p.name,
    CASE
    WHEN charindex('rx4dclaims', m.definition, 1 ) > 0 THEN 'rx4dclaims'
    ELSE
    NULL
    END as DB_STRUCTURE

    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


    EXEC sp_MSforeachdb 'SELECT ''?'' + ''.'' + B.name + ''.'' + A.name as Object_Name , A.type, A.create_date
    FROM [?].sys.objects A inner join [?].sys.schemas B on ( A.schema_id = B.schema_id ) 
    WHERE A.name like ''%spPrepareStratificationExtract%'''

  • 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 3 posts - 1 through 2 (of 2 total)

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