去掉SQL语句中的临时表

Question

我被要求"去掉"；SQL语句中的临时表，我已经尝试了所有的方法，但我还是个新手，有人能帮我吗？我需要保持最终结果不变，因为我使用final语句来填充光标。

/* build support temp tables from info mart schema */
SELECT 
TABLE_SCHEMA, TABLE_NAME AS META,    
SUBSTRING(TABLE_NAME, 1, LEN(TABLE_NAME) - 5) AS BASENAME 
INTO 
#TNS_META
FROM 
[INFORMATION_SCHEMA].[TABLES]
WHERE 
TABLE_NAME LIKE '%0_META%'


SELECT 
TABLE_SCHEMA, TABLE_NAME AS MEAS, 
SUBSTRING(TABLE_NAME, 1, LEN(TABLE_NAME) - 5) AS BASENAME
INTO 
#TNS_MEAS
FROM 
[INFORMATION_SCHEMA].[TABLES]
WHERE 
TABLE_NAME LIKE '%0_MEAS%'
/* final support table */
SELECT DISTINCT 
#TNS_META.TABLE_SCHEMA, MEAS, META
FROM 
#TNS_MEAS
FULL OUTER JOIN 
#TNS_META ON #TNS_META.BASENAME = #TNS_MEAS.BASENAME 

Answer 1

USE [IM_DM12]
;
WITH META (TABLE_SCHEMA, META, BASENAME)
AS
(
SELECT TABLE_SCHEMA, TABLE_NAME AS META, SUBSTRING(TABLE_NAME,1,LEN(TABLE_NAME)-5) AS BASENAME
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_NAME LIKE '%0_META%'
)
,
MEAS (TABLE_SCHEMA, MEAS, BASENAME)
AS
(
SELECT TABLE_SCHEMA, TABLE_NAME AS MEAS, SUBSTRING(TABLE_NAME,1,LEN(TABLE_NAME)-5) AS BASENAME
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_NAME LIKE '%0_MEAS%'
)
SELECT DISTINCT META.TABLE_SCHEMA, MEAS, META
FROM MEAS
FULL OUTER JOIN META ON META.BASENAME = MEAS.BASENAME
ORDER BY META.TABLE_SCHEMA;