Solved

Select column_name from INFORMATION.SCHEMA.COLUMNS not working with TEMP tables

Posted on 2016-10-07
3
52 Views
Last Modified: 2016-10-10
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???
0
Comment
Question by:OzarkSunshine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Author Comment

by:OzarkSunshine
ID: 41834039
Actually figured it out... had to get it to drop the brackets in the original temp table  name.
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41834040
Try this ...

Brackets issue

select column_name from TEMPDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='##E0404FF442A04AC7BCCEC3379FBC63'


select column_name from TEMPDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME LIKE '%##E0404FF442A04AC7BCCEC3379FBC63%'
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 41834076
You should stop using INFORMATION_SCHEMA views.  They have high overhead, including causing blocking.  [Yes, I know that when you look at their view definition they look like they shouldn't have any extra overhead, but in actual practice they do.]

SELECT c.name AS column_name
FROM tempdb.sys.columns c
WHERE c.object_id = OBJECT_ID('tempdb.dbo.##E0404FF442A04AC7BCCEC3379FBC63')
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Replacing unrecognized function name in SQL Query 4 44
Select Sum query with group by 8 42
Server 2012 r2 and SQL 2014 6 33
SQL Lag Function DateDiff 2 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question