Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column properties of table variables Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 6:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:58 PM
Points: 52, Visits: 171
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?
Post #1520037
Posted Thursday, December 5, 2013 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
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 2008, MVP
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

Post #1520044
Posted Thursday, December 5, 2013 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 12,888, Visits: 31,836
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520057
Posted Thursday, December 5, 2013 7:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
I thought I remembered object_Id working. Hmmm...


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1520080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse