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.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  February 22, 2010 at 2:06 am

  #1122767

  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

  -- Gianluca Sartori

• Shaun-884394

  Ten Centuries

  Points: 1096

  More actions

  February 22, 2010 at 5:14 am

  #1122858

  Thanks Gianluca for the quick reply.

  this is good.

  wish it had also given list of distinct matching words found in the object definition.

  😀

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  February 22, 2010 at 5:20 am

  #1122861

  In my humble opinion, this doesn't belong to SQL.

  You'd better do it in some other kind of language / technology.

  Not that I'm saying it can't be done, it's just that I don't think this is the right tool for the purpose.

  Just my two cents, anyway.

  -- Gianluca Sartori