Competition: What’s your favorite Redgate tool? Enter now.

Smart Find

danbocdanboc Posts: 23
edited November 27, 2008 7:58AM in SQL Refactor Previous Versions
Hi,

I was wondering if this feature has been thought about and (hopefully) under development.

I am trying to find a variable I used in one of my stored procs. It would be useful to have a search utility that would search accross the db and find all references of a search string in SPs, functions, triggers etc.

In anycase, is there a way to do this using the system SPs?

Thanks for your time!

Daniel

Comments

  • You can use the following SQL code which you can run against the specific database and it will tell you all of the objects that the variable exists in. Just change the search string to your chosen variable name.
    DECLARE @TextPart as varchar(255)
    
    SET @TextPart = 'SearchString'
    
    SELECT @TextPart = '%' + @TextPart + '%'
    
    SELECT DISTINCT Name, 
    	case 
    		when OBJECTPROPERTY(sysobjects.id, 'IsProcedure') = 1 then 'Stored Procedure'
    		when OBJECTPROPERTY(sysobjects.id, 'IsView') = 1 then 'View'
    		when OBJECTPROPERTY(sysobjects.id, 'IsInlineFunction') = 1 then 'Inline Function'
    		when OBJECTPROPERTY(sysobjects.id, 'IsScalarFunction') = 1 then 'Scalar Function'
    		when OBJECTPROPERTY(sysobjects.id, 'IsTableFunction') = 1 then 'Table Function'
    		when OBJECTPROPERTY(sysobjects.id, 'IsTrigger') = 1 then 'Trigger'
    	end as ObjectType
    FROM syscomments join sysobjects on sysobjects.id = syscomments.id
    WHERE PATINDEX(@TextPart , syscomments.text) > 0
    ORDER BY NAME
    

    I hope that helps.
  • Thanks for the code!

    Will be used. Heavily.

    D.
Sign In or Register to comment.