Script to search for a string in all DB objects (incl synonyms)

• jellybean

  SSCrazy

  Points: 2004

  More actions

  June 29, 2015 at 3:31 am

  #309529

  Hi,

  I'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?

  Any comments/suggestions - much appreciated,

  Colm

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 29, 2015 at 3:46 am

  #1808692

  Colm

  Just search in the definition column of sys.sql_modules. That'll cover stored procedures, views, functions, synonyms, triggers and so on, but not codeless objects such as tables and indexes. Beware that if the keyword does appear, it may be commented out or enclosed in quotes in the code.

  John

• Phil Parkin

  SSC Guru

  Points: 246686

  More actions

  June 29, 2015 at 4:02 am

  #1808697

  SQL Search[/url] from Redgate will do most of this for you. No code needed and it's free.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• jellybean

  SSCrazy

  Points: 2004

  More actions

  June 29, 2015 at 4:16 am

  #1808698

  Thanks John,

  I'm trying:

  Use DBName

  SELECT *

  FROM sys.sql_modules

  WHERE definition LIKE '%searchstring%'

  but its not finding the string! I can see the string in question inside the synonym. What am I doing wrong?

  Confused,

  Colm

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 29, 2015 at 4:28 am

  #1808699

  Colm

  Does your database have a case-sensitive collation?

  Use DBName

  SELECT *

  FROM sys.sql_modules

  WHERE UPPER(definition) LIKE UPPER('%searchstring%')

  John

• jellybean

  SSCrazy

  Points: 2004

  More actions

  June 29, 2015 at 5:02 am

  #1808702

  Hi,

  Yes SQL Search is free, indexes fast and is easy to use but does not detects string in the synonyms 🙁

• Lowell

  SSC Guru

  Points: 323504

  More actions

  June 29, 2015 at 5:35 am

  #1808711

  synonym definitions are not stored in sys.sql_modules.

  they are actually in the sys.synonyms table, so whatever search you create needs to search multiple places;

  you might want to include searching msdb.dbo.sysjobsteps, for example, if you are searchign definitions.

  SELECT

  'IF EXISTS(SELECT * FROM sys.synonyms WHERE name = '''

  + name

  + ''''

  + ' AND base_object_name <> ''' + base_object_name + ''')'

  + @vbCrLf

  + ' DROP SYNONYM ' + quotename(name) + ''

  + @vbCrLf

  +'GO'

  + @vbCrLf

  +'IF NOT EXISTS(SELECT * FROM sys.synonyms WHERE name = '''

  + name

  + ''')'

  + @vbCrLf

  + 'CREATE SYNONYM ' + quotename(name) + ' FOR ' + base_object_name +';'

  from sys.synonyms;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Grant Fritchey

  SSC Guru

  Points: 398679

  More actions

  June 29, 2015 at 5:51 am

  #1808717

  jellybean (6/29/2015)

  ---

  Hi,

  Yes SQL Search is free, indexes fast and is easy to use but does not detects string in the synonyms 🙁

  Put in a feature request.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• jellybean

  SSCrazy

  Points: 2004

  More actions

  June 29, 2015 at 6:59 am

  #1808740

  Hi everyone,

  Thanks for all the comments. The following script appears to work for my needs:

  use DBName

  select * from sys.synonyms

  where base_object_name LIKE '%SearchString%'

  Ideally, I would like the ability to automatically cycle thru all databases present - without having to change the use <DBName> syntax. Anyone on that?

  Thanks.

  PS - Yes I will submit a request with the folk at Red Gate SQL Search.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  June 29, 2015 at 7:16 am

  #1808746

  jellybean (6/29/2015)

  ---

  Hi everyone,

  Thanks for all the comments. The following script appears to work for my needs:

  use DBName

  select * from sys.synonyms

  where base_object_name LIKE '%SearchString%'

  Ideally, I would like the ability to automatically cycle thru all databases present - without having to change the use <DBName> syntax. Anyone on that?

  Thanks.

  PS - Yes I will submit a request with the folk at Red Gate SQL Search.

  that requires a cursor of some sort;

  just use the built in sp_msForEachDB:

  EXEC sp_msForEachDb 'select ''?'' As DbName, * from [?].sys.synonyms

  where base_object_name LIKE ''%SearchString%'' '

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jellybean

  SSCrazy

  Points: 2004

  More actions

  June 29, 2015 at 7:19 am

  #1808751

  Nice Lowell. Works like a charm.

  Thank you.