I've got various databases, and what to be sure I am removing something (a view in this case) that is truly orphaned. Is the the correct SQL to be using:
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.routine_definition LIKE '%my_view_name%'
FROM SYSCOMMENTS t
WHERE CHARINDEX('my_view_name', t.text) > 0
You have one option only.
select object_name(m.object_id), m.* from sys.sql_modules m where m.definition like N'%my_view_name%'
syscomments and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "myViewName" is used at position 3998, it won't be found. syscomments does have multiple lines but ROUTINES truncates.