Get definitive names for columns from table variable

Question

I can declare a table variable as such:

DECLARE @tv_source TABLE(c1 int, 
providerName varchar(50),
providerSMS varchar(50))

If I then execute the following, I see the table name similar to: "#468862B0"

select top 1 * from tempdb.sys.tables where type = 'U' order by create_date desc
select TOP 1 name,* from tempdb.sys.sysobjects ORDER BY CRDATE desc

If I then immediately execute:

select TOP 3 * 
from tempdb.sys.columns 
where object_id in (select TOP 1 object_id from tempdb.sys.tables ORDER BY Create_date desc)

I see the columns I declared above for the table variable.

My question is, is there any way to definitively associate those columns with the name I declared in the table declaration above "@tv_source"?

In a normal table, you would see the actual name but, as noted above, table variables get morphed into a hex value (which, btw is the hex value of the object_id).

Comte

@HamletHakobyan table variables ARE ALWAYS created in tempdb – Comte

@PinnyM I don't think dynamic sql will help, but I want to see the information from the database, not from the text of dynamic sql itself - keep in mind the scope of table variables here. That said, I am willing to view any solution :) – Comte

There is no way of going from the name @tv_source to the metadata in tempdb. If the table variable contains at least one row you can use my answer here to get the object_id. – Crosstie

@MartinSmith - thanks, one somewhat interesting way; always create a GUID column name :) – Comte

The second part of my answer doesnt rely on that. Just requires the table to be non empty. If that isn't suitable please explain why you actually need this. – Crosstie

I can understand needing to look up metadata sometimes in a query, where it may be working against unfamiliar objects; but table variables only last for a single batch. What sort of code are you writing where you're losing track of the variable's declaration in a single batch? – Periphrasis

I can think of a few ways of achieving something approximating this for specific use cases but the failure to explain the exact context in which you need this makes this question unanswerable IMO (except with the answer "no"). – Crosstie

@MartinSmith for some context that took me down this tract, see this question: #14328853 as well as several instances of "prove/proof" that these are generated in the tempdb. As to my intended use, I don't have a specific, but sometimes people ask questions they see no answer for after research simply to gain knowlege and understanding on a subject matter. Your answer on that other question does provide some alternative as well, thank you for adding that as well. – Comte

@MarkSchultheiss - Ah OK. Other things you can look at are the execution plan XML when using undocumented trace flag 8666 (contains the internal name such as #468862B0) and the transaction name in the tempdb transaction log. – Crosstie

Answer 1

You can query your table variable top(0) with an outer apply from one row using for xml path('') and then query the XML for the element names.

This will work as long as your column names does not have names that is invalid XML element names. The column names can for instance not use ampersand or space.

declare @tv_source table
(
  c1 int, 
  providerName varchar(50),
  providerSMS varchar(50)
)

select TN.N.value('local-name(.)', 'sysname') as ColumnName
from 
  (
  select TV.*
  from (select 1) as D(N)
    outer apply (
                select top(0) *
                from @tv_source
                ) as TV
  for xml path(''), elements xsinil, type
  ) as TX(X)
cross apply TX.X.nodes('*') as TN(N)

Another option would be to use the xmlschema directive of for xml auto. This solution does handle invalid XML characters but they are escaped so if you have a column name with a space like [provider Name] the result will be provider_x0020_Name.
You need to store the resulting XML to a variable and query that for the information you want.

declare @XML xml;

set @XML = 
  (
  select top(0) *
  from @tv_source
  for xml auto, xmlschema, type
  );

with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xsd)
select T.X.value('@name', 'sysname')
from @XML.nodes('//xsd:attribute') as T(X);

The XML created by xmlschema contains more information that might be of interest. You can retrieve the table variable name and the datatypes as well.

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet12" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet12" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="_x0040_tv_source">
    <xsd:complexType>
      <xsd:attribute name="c1" type="sqltypes:int" />
      <xsd:attribute name="providerName">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1035" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="50" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="providerSMS">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1035" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="50" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Ma

Answer 2

I see from your comments that this exercise is for learning so you don't have a specific use case or need. That said, another way to get detailed column meta-data from a table variable variable is with sp_describe_first_result_set.

EXEC sp_describe_first_result_set @tsql =  N'
declare @tableName table (ID bigint,
                            Column1 bigint,
                            Column2 datetime,
                            createdBy nvarchar(100),
                            dateAdded nvarchar(12),
                            Type nvarchar(10)
                            )
SELECT * FROM @tableName;';

Invalidate

Did you mean to answer this link? – Speaker