hi,
to do this i wrote this query:
declare @d varchar(max)='this is my query'
declare @d_trim varchar(max)=replace(replace(replace(replace(REPLACE(@d,CHAR(13),''),char(10),''),' ',''),'[',''),']','')
select *
from
(
select CHARINDEX(t1.name,@d)c,
SUBSTRING(@d,CHARINDEX(t1.name,@d)-5,LEN(t1.name)+10)x,
SUBSTRING(@d,CHARINDEX(t1.name,@d)-1,1)L,
ascii(SUBSTRING(@d,CHARINDEX(t1.name,@d)-1,1))L_ascii,
SUBSTRING(@d,CHARINDEX(t1.name,@d)+LEN(t1.name),1)R,
ascii(SUBSTRING(@d,CHARINDEX(t1.name,@d)+LEN(t1.name),1))R_ascii,
SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-1,1)S_val,
case when SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-1,1)in('.') then 1 else 0 end S_exists,
SUBSTRING(@d_trim,CHARINDEX(t1.name,@d_trim)-LEN(t2.name)-1,LEN(t2.name))S,
t1.*,
t2.name sch
--select *
from sys.objects t1
join sys.schemas t2
on t1.schema_id=t2.schema_id
)t1
where type IN ('P','FN', 'IF', 'TF','V','U')
and @d like '%'+name+'%'
--i exclude the objects that could be something like 'object2'/'_object'/...
and L in('','[','.',CHAR(13),char(10))
and R in('',']',CHAR(13),char(10))
--i check the schema if defined
and case S_exists when 1 then S else '' end=case S_exists when 1 then sch else '' end