Smart Find

danboc

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

Ben Pinchen

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.

danboc

Thanks for the code!

Will be used. Heavily.

D.