search for a string in definitions of all objects in all databases

Posted on : 16-12-2011 | By : Devi Prasad | In : SQL Server Basics

Share:

0


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:

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

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:

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

And now, if you wanted to display the database name also in the output.
Below is the query for that:

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

Making the query mode advanced by adding raiserror to check the databases scanned.

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
(Visited 24 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , ,