Find all occurances of a word starting with xyz_

Question

Post reply

Find all occurances of a word starting with xyz_

Shaun-884394

Ten Centuries

Points: 1096
More actions
February 21, 2010 at 11:21 pm

#139030

Hi all,

recently i was asked to find out dependencies of objects starting with XYZ_

In my project we are phasing out all the sql server objects(tables/procedures/functions/views/synonyms) starting with XYZ_ and replace them with somthing else. now i am required to find out all the objects which depend on the above objects.

i used this query to find them.

-- to find out all objects having references to XYZ_ SELECT DISTINCT o.name ,o.xtype, c.TEXT FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '% XYZ_%' -- to find out all jobs having references to XYZ_ use msdb select A.name Job_Name, s.step_id, s.step_name,s.command from sysjobs A ,sysjobschedules B, sysjobsteps S where A.job_id = B.job_id and s.job_id = a.job_id --and a.enabled = 1 and s.command like '% XYZ_%'

This works, but it gives me the complete definition of those objects. Now i need to check each definition one by one to find the object and its occurrences. The output i expect is, it should give the list of objects starting with XYZ_ found in these definitions and number of times they have occurred.

Could somebody help me out here.

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

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

This should do the trick. There must be some quicker way, but for a one time search it should work.



DECLARE @searchString varchar(100)

DECLARE @searchStringEscaped varchar(100)

SELECT @searchString = ' XYZ_',

	@searchStringEscaped = '% XYZ[_]%'

;WITH searchObjects AS (

	SELECT object_name(id) as name,

		object_definition(id) AS definition

	FROM sysobjects

	UNION ALL

	SELECT A.name + '.' + s.step_name, s.command

	FROM msdb.dbo.sysjobs A ,msdb.dbo.sysjobschedules B, msdb.dbo.sysjobsteps S

	WHERE A.job_id = B.job_id

		AND s.job_id = a.job_id

)

SELECT

	name,

	definition,

	(

		--length of original object definition

		LEN(definition) -

		--length of object definition without any occurrences of the search string

		LEN(REPLACE(definition, @searchString,''))

	)

	-- length of the search string

	/ LEN(@searchString) AS numFind

FROM searchObjects

WHERE definition LIKE @searchStringEscaped