June 29, 2015 at 3:31 am
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
June 29, 2015 at 3:46 am
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
June 29, 2015 at 4:02 am
June 29, 2015 at 4:16 am
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
June 29, 2015 at 4:28 am
Colm
Does your database have a case-sensitive collation?
Use DBName
SELECT *
FROM sys.sql_modules
WHERE UPPER(definition) LIKE UPPER('%searchstring%')
John
June 29, 2015 at 5:02 am
Hi,
Yes SQL Search is free, indexes fast and is easy to use but does not detects string in the synonyms 🙁
June 29, 2015 at 5:35 am
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
June 29, 2015 at 5:51 am
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
June 29, 2015 at 6:59 am
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.
June 29, 2015 at 7:16 am
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
June 29, 2015 at 7:19 am
Nice Lowell. Works like a charm.
Thank you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply