ServerName		DatabaseName		SchemaName		TableName

SQLEDW			PRE_KS_ODS			dbo				Events
SQLEDW			PRE_MS_ODS			dbo				Events
NULL			edw				dbo				MASTER_EVENT 
SQLEDW			edw				dbo				MSEDR_DS_TBL_D

DECLARE @Tmp TABLE (Id INT,Name VARCHAR(500)) 
INSERT @Tmp SELECT 1,'from SQLEDW.PRE_KS_ODS.dbo.Events et ' 
INSERT @Tmp SELECT 2,'join SQLEDW.PRE_MS_ODS.dbo.Event_Methods el on el.id = et.Event_ID and et.load_date = el.LOAD_DATE ' 
INSERT @Tmp SELECT 3,'edw.dbo.MASTER_EVENT a , ' 
INSERT @Tmp SELECT 4,' (SELECT ''SQLEDW.edw.dbo.MSEDR_DS_TBL_D'' AS TableName, COUNT(*) AS EventNo '
INSERT @Tmp SELECT 3,'edw.dbo.MASTER_EVENT' 

select name, _1st.blank, _1st.dot, name.start, just.thename, _4part.*
from @tmp
cross apply (select charindex(' ', name), charindex('.', name)) _1st(blank, dot)
cross apply (
	select substring(name,
				case 
				    when _1st.blank = 0
						then 1
					when _1st.dot = 0
						then _1st.blank + 1
					when _1st.dot > _1st.blank
					    then _1st.blank + 1
					else 1
				end,
				len(name) )
	) name(start)
cross apply (select CHARINDEX(' ',name.start)) _2nd(blank)
cross apply (
	select left(name.start,
				case when _2nd.blank = 0
				    then len(name.start)
				else _2nd.blank -1
				end)
	) just(thename)
cross apply (
	select PARSENAME(just.thename, 4) as ServerName
	     , PARSENAME(just.thename, 3) as DatabaseName
		 , PARSENAME(just.thename, 2) as SchemaName
		 , PARSENAME(just.thename, 1) as TableName
	) _4part