Smart Find

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

Smart Find

Postby danboc » Tue Nov 25, 2008 10:04 am

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
danboc
 
Posts: 16
Joined: Thu May 01, 2008 1:57 pm

Postby Ben Pinchen » Thu Nov 27, 2008 1:52 pm

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.
Code: Select all
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.
Ben Pinchen
 
Posts: 157
Joined: Thu Dec 28, 2006 1:21 pm
Location: Red Gate

Postby danboc » Thu Nov 27, 2008 1:58 pm

Thanks for the code!

Will be used. Heavily.

D.
danboc
 
Posts: 16
Joined: Thu May 01, 2008 1:57 pm


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests