这将起作用,只要您的列名没有无效XML元素名称的名称即可.例如,列名不能使用&符号或空格. declare @tv_source table( c1 int, providerName varchar(50), providerSMS varchar(50))select TN.N.value('local-name(.)', 'sysname') as ColumnNamefrom ( 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) 另一个选择是使用for xml auto的xmlschema指令.此解决方案确实处理了无效的XML字符,但是它们被转义了,因此,如果您的列名带有空格,例如[provider Name],则结果将为provider_x0020_Name.您需要将结果XML存储到变量中,并查询所需的信息. 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); xmlschema创建的XML包含可能感兴趣的更多信息.您还可以检索表变量名称和数据类型. <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> 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 descselect TOP 1 name,* from tempdb.sys.sysobjects ORDER BY CRDATE descIf I then immediately execute:select TOP 3 *from tempdb.sys.columnswhere 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 ColumnNamefrom ( 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> 这篇关于从表变量获取列的明确名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!