Many times we face a typical problem of searching for a string the definitions of all objects in all the databases.
Real time scenario:
You wanted to search for all the objects in all databases that have used the word test in its definition.
Solution:
TSQL Query:
[sql]
DECLARE @vQuery VARCHAR(500)
SET @vQuery = ‘
USE ?
SELECT name as ObjectName FROM [?].sys.objects O
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ”%test%”
‘
exec sp_MSforeachdb @vQuery
[/sql]
The above query returns the names of all objects that have a string/text as ‘test’.
If you wanted to display the type of the object also, then below is the query:
[sql]
DECLARE @vQuery VARCHAR(500)
SET @vQuery = ‘
USE ?
SELECT name as ObjectName,O.type_desc as ObjectType FROM [?].sys.objects O
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ”%test%”’
exec sp_MSforeachdb @vQuery
[/sql]
And now, if you wanted to display the database name also in the output.
Below is the query for that:
[sql]
DECLARE @vQuery VARCHAR(500)
SET @vQuery = ‘
USE ?
SELECT ”?” as DatabaseName,name ObjectName,O.type_desc as ObjectType FROM [?].sys.objects O
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ”%a%”’
exec sp_MSforeachdb @vQuery
[/sql]
Making the query mode advanced by adding raiserror to check the databases scanned.
[sql]
DECLARE @vQuery VARCHAR(500)
SET @vQuery = ‘
USE ?
raiserror(”Searching Database ?”,1,1)
SELECT ”?” as DatabaseName,name ObjectName,O.type_desc as ObjectType FROM [?].sys.objects O
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ”%a%”’
exec sp_MSforeachdb @vQuery
[/sql]