Red Gate forums :: View topic - Smart Find
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Refactor 1
SQL Refactor 1 forum

Smart Find

 Search in SQL Refactor 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
danboc



Joined: 01 May 2008
Posts: 16
PostPosted: Tue Nov 25, 2008 10:04 am    Post subject: Smart Find Reply with quote
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
Back to top
View user's profile Send private message
Ben Pinchen



Joined: 28 Dec 2006
Posts: 157
Location: Red Gate
PostPosted: Thu Nov 27, 2008 1:52 pm    Post subject: Reply with quote
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:

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.
Back to top
View user's profile Send private message
danboc



Joined: 01 May 2008
Posts: 16
PostPosted: Thu Nov 27, 2008 1:58 pm    Post subject: Reply with quote
Thanks for the code!

Will be used. Heavily.

D.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group