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).
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>