I'm creating a global temp table using a random id and then trying to get column names from that table using INFORMATION_SCHEMA. Below is an example of what I'm doing --
SELECT 'JOHN' AS FIRST_NAME,'DAVIS' AS LAST_NAME ,'CHICAGO' AS CITY INTO [##E0404FF442A04AC7BCCEC3379FBC63]
select column_name from TEMPDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='[##E0404FF442A04AC7BCCEC3379FBC63]'
This does not work, it returns nothing under column_name. I can take that same table and copy it into another temp table, and it works.
SELECT * INTO ##OUT_LABEL FROM [##E0404FF442A04AC7BCCEC3379FBC63]
select column_name from TEMPDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='##OUT_LABEL'
column_name
FIRST_NAME
LAST_NAME
CITY
What am I missing here on why this is not working???