SQL Server Performance

How To Locate a String That will be Occur in The Current Database?

Discussion in 'SQL Server 2008 General Developer Questions' started by seemun, Mar 10, 2010.

  1. seemun New Member

    Hi,
    I want to retrieve a string PONo (example) which have been occurred in which table, store procedure, function, trigger and etc in the current database. I would expecting the SQL return the Name, Owner, Object_Type as below example.
    example output:
    NameOwnerObject_Type
    po_POMasterdboUser Table
    po_PODetaildboUser Table
    sp_GetPONodboStore Procedure
    fc_Return_SummarydboFunctionPlease advice the code sample to retrieve the above record.
    rgds/sm
  2. Madhivanan Moderator

    select
    name,schema_name(schema_id) as [schema_name],case
    when type='u' then 'User table' when type='p' then 'Stored Procedure'
    when type='tr' then 'Trigger' when type='fn' then 'Function'
    end
    as object_typefrom
    sys.objectswhere
    type in ('u','p','tr','fn')
  3. seemun New Member

    Hi,
    The below script only display all the table, store proc, trigger and function name out, however I want to filter record only when PO_NO exists instead of display all record (table, store proc, trigger and function). Please advice.select name, schema_name(schema_id) as [schema_name],case when type='u' then 'User table' when type='p' then 'Stored Procedure' when type='tr' then 'Trigger' when type='fn' then 'Function' end as object_typefrom sys.objects
    where type in ('u','p','tr','fn')
  4. Madhivanan Moderator

    add this condition at the where clause
    AND name like 'PO_NO%'
  5. moh_hassan20 New Member

    if you search for the string "PONo" inside the object name , reply by Madhivanan is enough.
    if you search for "PONo" inside the sql definition of the objects , you can use the following query:
    SELECT OBJECT_NAME(sm.object_id) Name,
    o.type_desc,
    schema_name (o.schema_id) Owner
    FROM sys.sql_modules AS sm
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    where sm.definition like '%PONo%'
    ORDER BY 1
  6. seemun New Member

    Hi,
    If I use this script as below, i found no record return in fact i have this PONo exist in many store proc, view, table and funtions?
    ===================================================================Select name, schema_name(schema_id) as [schema_name],case when type='u' then 'User table' when type='p' then 'Stored Procedure' when type='tr' then 'Trigger' when type='fn' then 'Function' when type='v' then 'View' end as object_typefrom sys.objects where type in ('u','p','tr','fn','v')
    AND [name] like 'PONo%'
    ===================================================================
    Well i tried to execute below script and it only return me the PONo existed in Store Procedure, Funtions and Views but no record return for Table.
    Please advice how to return record for PONo exist in table, I need to know which table contains PONo also
    ===================================================================SELECT OBJECT_NAME(sm.object_id) Name, o.type_desc, schema_name (o.schema_id) OwnerFROM sys.sql_modules AS smJOIN sys.objects AS o ON sm.object_id = o.object_idwhere sm.definition like '%PONo%'
    ORDER BY 1
    ===================================================================
  7. moh_hassan20 New Member

    This query retun all objects including 'PONo'
    SELECT o.object_id, OBJECT_NAME(o.object_id) Name, o.type_desc, schema_name (o.schema_id) Owner
    From sys.objects AS o
    LEFT JOIN sys.sql_modules AS sm on sm.object_id = o.object_id
    WHERE sm.definition like '%PONo%'
    or OBJECT_NAME(o.object_id) like '%PONo%'
    ORDER BY 1

Share This Page