Last idea: follow the rule of KISS (Keep It Simple & Stupid) write store procedure formatting it with the following rule: every command that reference a table should be on one line and should be the only one, e.g.

UPDATE table1

..

SELECT a,b,c

FROM dbo.table2

CROSS JOIN server1.mydb.dbo.TABLE3

FULL JOIN zzzzz

DELETE FROM #kkk

INSERT INTO somedb.dbo.tableX

(

A,b,c

) select x,y,z

FROM ANOTHERTABLE

So, your scanning routine should discard comments and searching only for DML. At last, replace commands with '', trim it and you’ll get the tablename + eventually alias for the table.

Here you are a sample:

create table #a (t varchar(8000))

delete #a

insert #a exec sp_helptext ‘mysp_elab’

update #a set

t = rtrim(ltrim(replace(t,char(9),' ')))

-- write here code to cut out comments

-- ...

-- ...

-- end

delete from #a

WHERE t not like 'delete %'

and t not like 'update %'

and t not like 'insert %'

and t not like 'from %'

and t not like 'JOIN %'

SELECT

ltrim

(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(t,'delete ','')

,'update ','')

,'insert ','')

,'from ','')

,'JOIN ','')

,'INNER ','')

,'LEFT ','')

,'RIGHT ','')

,'FULL ','')

)

FROM #a

With this method you can find also table in quoted dynamic sql.