Find dependencies between field and sp/function

Question

Find dependencies between field and sp/function

halifaxdal

SSCoach

Points: 19741
More actions
April 8, 2014 at 9:20 pm

#287506

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

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

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

Dave Vroman SSC Eights! Points: 821 More actions · Answer 1

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: 4319 More actions · Answer 2

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 SSCommitted Points: 1552 More actions · Answer 3

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]