How to determine internal name of table-valued variable in MS SQL Server 2005

Question

The name of a temporary table such as #t1 can be determined using

SELECT @TableName = [Name] FROM tempdb.sys.tables 
    WHERE [Object_ID] = object_id('tempDB.dbo.#t1')

How can I find the name of a table valued variable, i.e. one declared by

declare @t2 as table ( a int)

the purpose is to be able to get meta-information about the table, using something like

SELECT @Headers = dbo.Concatenate('[' + c.[Name] + ']')  
  FROM  sys.all_columns c
     inner join sys.tables t
        on c.object_id = t.object_id
  where t.name = @TableName

although for temp tables you have to look in tempdb.sys.tables instead of sys.tables. where do you look for table valued variables?

Answer 1

I don't believe you can, as table variables are created in memory not in tempdb.

Answer 2

From Books Online:

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

Given this, there should be no need to look up this value at run-time because you have to know it at design-time.

Answer 3

I realize now that I can't do what I wanted to do, which is write a generic function for formatting table valued variables into html tables. For starters, in sql server 2005 you can't pass table valued parameters:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

moreover, in sql server 2008, the parameters have to be strongly typed, so you will always know the number and type of columns.

Answer 4

On the topic of passing arbitrary lists/arrays into a SQL Server 2005 function or sproc,
the least hokey way I know is to use an XML variable. If desired, that XML variable can be a strongly typed XML type that is associated w/ an XML Schema.

Given a list passed into a procedure/function as XML, you can extract that list into a table variable or temp table via "shredding". "To shred" XML means to transform in the opposite direction--from XML to rowset(s). (The FOR XML clause causes a rowset to XML transformation.)

In the user-defined table function


CREATE FUNCTION [dbo].[udtShredXmlInputBondIdList]

(
  -- Add the parameters for the function here
  @xmlInputBondIdList xml
)
RETURNS
@tblResults TABLE
(
  -- Add the column definitions for the TABLE variable here
  BondId int
)
AS
BEGIN
  -- Should add a schema validation for @xmlInputIssuerIdList here
  --Place validation here
  -- Fill the table variable with the rows for your result set
  INSERT @tblResults
  SELECT
    nref.value('.', 'int') as BondId
  FROM
    @xmlInputBondIdList.nodes('//BondID') as R(nref)
  RETURN
END

if the @xmlInputBondIdList is an XML fragment of the expected structure like that immediately below and is invoked as follows

DECLARE @xmlInputBondIdList xml
SET @xmlInputBondIdList =
'
<XmlInputBondIdList>
  <BondID>8681</BondID>
  <BondID>8680</BondID>
  <BondID>8684</BondID>
</XmlInputBondIdList>
'
SELECT *
FROM [CorporateBond].[dbo].[udtShredXmlInputBondIdList]
(@xmlInputBondIdList)

the result will be the rowset

BondId
8681
8680
8684

A couple other examples can be found at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=678284&SiteID=1