querying sys_modules from master (not a particular databae) in T-SQL

As best as I can tell...

USE Yin;
GO
DECLARE @Search varchar(255)
SET @Search='Yang'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1

 
 

...may be rewritten like so...

DECLARE @Search varchar(255)
SET @Search='Yang'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM [Yin].sys.sql_modules m
INNER JOIN [Yin].sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1

 
 

...you just have to enclose the database name in square brackets. This suggests you cannot use OBJECT_NAME() and OBJECTPROPERTY() when switching databases in this shape and if you attempt to do so it will not work. You have to find a workaround for these tricks.