Is there a way to get the fields names of a table created in a function / stored procedure?

Question

Hello...

when I need the columns of an existing table I use the query:

SELECT c.[name]
FROM
    (SELECT * from syscolumns) c
     INNER JOIN
    (SELECT [id] from sysobjects where name= 'tableName') o on c.[id]=o.[id]

I need the fields of a table that I create during runTime:

select    
    a.ID,
    b.lName,
    b.fName
into #T
from 
    a
        inner join
    b on a.id=b.id

.

select * from #T_columns

will result a table with 3 rows: id lName fName

How can I do it?

Thanks

Answer 1

When you create a temp table, it will be in tempdb. You can look it up like this:

SELECT COLUMN_NAME
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME LIKE '#T|_%' ESCAPE '|'

If you do a SELECT * FROM INFORMATION_SCHEMA.TABLES in tempdb, you'll see the temp table name you use (#T) actually has a number of underscores appended to it followed by a unique identifier. So you won't find it it you just search where table_name = '#T'.

So that's why you have to use a LIKE as I've shown above. This will match on "#T_" followed by any other other characters.

Answer 2

Try this

SELECT sc.NAME
FROM 
tempdb..SYSOBJECTS so JOIN
tempdb..SYSCOLUMNS sc ON sc.id = so.id 
WHERE so.NAME LIKE '#T%'