Find dependencies between field and sp/function

  • halifaxdal

    SSCoach

    Points: 19584

    Comments posted to this topic are about the item Find dependencies between field and sp/function

  • Dave Vroman

    SSC Eights!

    Points: 821

    I'm curious if you might want to fold this script into yours. I've used one like this for years to search for strings in the database objects.

    -- Search Stored Procedures Containing Text

    SELECT OBJECT_NAME(sc.id) ObjectName,

    CASE WHEN OBJECTPROPERTY(sc.id, 'IsReplProc') = 1 THEN 'Replication Stored Procedure'

    WHEN OBJECTPROPERTY(sc.id, 'IsExtendedProc') = 1 THEN 'Extended Stored Procedure'

    WHEN OBJECTPROPERTY(sc.id, 'IsProcedure') = 1 THEN 'Stored Procedure'

    WHEN OBJECTPROPERTY(sc.id, 'IsTrigger') = 1 THEN 'Trigger'

    WHEN OBJECTPROPERTY(sc.id, 'IsTableFunction') = 1 THEN 'Table-Valued Function'

    WHEN OBJECTPROPERTY(sc.id, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'

    WHEN OBJECTPROPERTY(sc.id, 'IsInlineFunction') = 1 THEN 'Inline function'

    END AS ObjectType,

    so.create_date,

    so.modify_date,

    sc.[text]

    --, *

    -- SELECT *

    FROM sys.syscomments AS sc

    INNER JOIN sys.objects AS so ON sc.id = so.object_id

    WHERE sc.[TEXT] LIKE '%string%'

    -- AND sc.[TEXT] NOT LIKE '%string2%'

    AND (OBJECTPROPERTY(sc.id, 'IsProcedure') = 1

    OR OBJECTPROPERTY(sc.id, 'IsReplProc') = 1

    OR OBJECTPROPERTY(sc.id, 'IsExtendedProc') = 1

    OR OBJECTPROPERTY(sc.id, 'IsTrigger') = 1

    OR OBJECTPROPERTY(sc.id, 'IsTableFunction') = 1

    OR OBJECTPROPERTY(sc.id, 'IsScalarFunction') = 1

    OR OBJECTPROPERTY(sc.id, 'IsInlineFunction') = 1)

  • peter.row

    SSCarpal Tunnel

    Points: 4296

    We used a very similar code except that we output the schema, routine name and type (e.g. sp, function):

    set nocount on

    declare @SearchFor varchar (100)

    set @SearchFor = 'FieldNameForExample'

    select distinct

    object_schema_name(SO.Id) as [Schema],

    SO.[Name],

    case rtrim (XType)

    when 'TF' then 'Function'

    when 'FN' then 'Function'

    when 'P' then 'Stored procedure'

    when 'TR' then 'Trigger'

    when 'V' then 'View'

    else rtrim (XType)

    end as [Type]

    from

    sysobjects SO

    inner join syscomments SC on SO.[ID] = SC.[ID]

    where

    SC.text like '%' + @SearchFor + '%'

    order by

    [Schema],

    [Type],

    [Name]

    I don't see the real benefit of check whether it is used as a parameter or not - I mean what happens if it appears as both a parameter and in the body of the SP/func/etc...?

    With the above script I simply run it and then for each of the results I open the individual script file I have for it in SQL Management Studio, find the search term and update as necessary, search code for any places that used the SP (if it's an SP), run the above using the result I updated incase of SPs calling other SPs.

  • phegedusich

    Ten Centuries

    Points: 1343

    If you simply want to check all of the user-developed objects, use this for the "c" cursor:

    declare c cursor for

    SELECT name, 'sp'

    FROM sys.procedures

    where is_ms_shipped = 0

    UNION

    SELECT name, 'fn'

    FROM sys.objects

    WHERE is_ms_shipped = 0 order by [name]

Viewing 4 posts - 1 through 4 (of 4 total)

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