create table #temp1 (query nvarchar(max))
insert into #temp1
values ('  SELECT *    FROM table1')
	, ('  SELECT col1, col23    FROM table2 a join table3 b on a.col1 = b.col1')

create table #temp1 (query nvarchar(max))
insert into #temp1
values ('  SELECT *    FROM dbo.table1')
	, ('  SELECT col1, col23    FROM dbo.table2 a join dbo.table3 b on a.col1 = b.col1')

;WITH rCTE (tablename, query, n) AS (
    SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))
        ,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1))
        ,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM #temp1
    CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)
    UNION ALL
    SELECT SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' '))
        ,SUBSTRING(str1, CHARINDEX(' ', str1 + ' '), 1+LEN(str1)), n
    FROM rCTE
    CROSS APPLY (SELECT SUBSTRING(query, CHARINDEX('dbo.', query), LEN(query))) x (str1)
    WHERE query <> '' AND SUBSTRING(str1, 1, CHARINDEX(' ', str1 + ' ')) <> '')
SELECT DISTINCT tablename
FROM rCTE

DROP TABLE #temp1

create table #temp1 (query nvarchar(max))
insert into #temp1
values ('  SELECT *    FROM dbo.table1')
	, ('  SELECT col1, col23    FROM dbo.table2 a join dbo.table3 b on a.col1 = b.col1')

insert into #temp1
values ('  SELECT *    FROM table1')
	, ('  SELECT col1, col23    FROM table2 a join table3 b on a.col1 = b.col1')

SELECT * FROM #temp1
CROSS APPLY dbo.DelimitedSplit8K(query,' ') T1
CROSS APPLY dbo.DelimitedSplit8K(query,' ') T2
WHERE T1.ItemNumber + 1 = T2.ItemNumber
AND T1.Item IN('JOIN','FROM')