Query - to find objects containing specific word

Sometime back we did one exercise where our objective was to find out the objects which don't work any more .. 

for example suppose we had created a view v_tab with a simple select statement fetching abc column from XYZ table .. later on say after 3-4 months that abc column got renamed to lmn but the view v_tab referring to table column as abc never got updated so that view now became obsolete .. You see what happened there and mind you this happens quite often when attention is not paid while deploying new changes to the database objects.. 

we used following approach to address this

• Create blank database with same name on some other server
• Generate scripts for all the objects for the database using Gen script wizard on the original database
• Apply those scripts to blank database
• If there is an error on the object creation record the object name and error received
• comment the entire code for the object  and add 

''-- done by DB team

select 1 as nums" 

• re-run the script to create that object with above 2 lines of code
• once the exercise is done for the database rename database to test_dbname

But the person who did this exercise did not record everything... it was obvious that he did not like this task but somebody had to do it ..Our manager wanted that info so we had to find a way to fetch it and we did .. using following script

The script does following things

• Loops over database starting with name test
• Looks for -- done by DB team in the object definition
• returns the required info

IF OBJECT_ID('tempdb..#objects') IS NOT NULL

DROP TABLE #objects

CREATE TABLE #objects

(

DBName VARCHAR(100),

ObjectName VARCHAR(1000),

objectType VARCHAR(100),

ObjectDef VARCHAR(MAX)

)

INSERT INTO #objects (DBName,ObjectName,objectType,Objectdef)

EXEC sp_msforeachdb

'

use ?

IF (''?'' in (SELECT name FROM sys.databases WHERE Name like ''test%'' ))

BEGIN

print ''?''

SELECT ''?'',name,type_desc,OBJECT_DEFINITION(OBJECT_ID)

FROM ?.SYS.OBJECTS

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%done%by%team%''

END

'

SELECT * FROM #objects

order by DBName,objectType