Column properties of table variables

Question

Column properties of table variables

N_Muller

SSCarpal Tunnel

Points: 4037
More actions
December 5, 2013 at 6:53 am

#280765

When I need to know the properties of a column, I can query sys.columns for a given object_id and name. Is there something similar to sys.columns for table variables?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sys.columns 🙂

Table variables have their columns recorded in the TempDB system tables just like any other table. They're a little harder to identify because the table name is changed. Edit: Remainder removed.

They'll only be there while the table variable is in scope though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323506 More actions · Answer 2

Gail i just tried that, but cannot seem to get the object_id;

for a temp table, i can get object_id('tempdb.dbo.TableName') no problem, but is there a trick for table variables?

this is my sample code

i can get the name of the table (in my case it was #5EC4D4C8) by querying sys.columns for the column name i know exists, but not via the object_id yet?

DECLARE @Example Table(LongAddress varchar(500) );

insert into @Example

SELECT ' Some stuff ';

--cannot seem to get the object_id?

print convert(varchar,object_id('tempdb.dbo.@Example'));

print convert(varchar,object_id('@Example'));

declare @id int = object_id('tempdb.dbo.@Example');

--in theory, if i had the id i could get the

SELECT

tabz.name,

colz.*

FROM tempdb.sys.tables tabz

inner join tempdb.sys.columns colz

on tabz.object_id = colz.object_id

WHERE tabz.object_id =@id; --mine was null

--WHERE tabz.name LIKE '%Example%' -- not working

--WHERE colz.name = 'LongAddress' --works

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I thought I remembered object_Id working. Hmmm...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass