How to determine the internal name of a table-value variable in MS SQL Server 2005

Question

The name of the 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 variable depending on a table, i.e. announced

 declare @t2 as table (a int) 

The goal 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 temporary tables you need to look for tempdb.sys.tables instead of sys.tables . where are you looking for table variables with tables?

---

Now I understand that I can’t do what I would like to do, which is a general function for formatting table variables in html tables. Firstly, in sql server 2005 you cannot pass tabular parameters:

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

In addition, in SQL Server 2008, parameters must be strongly typed, so you will always know the number and type of columns.

Answer 1

I do not think you can, since table variables are created in memory not in tempdb.

Answer 2

Table variable metadata can also be viewed in tempdb.sys.tables . This is easy to verify from the below.

 declare @t2 as table ( [38F055D8-25D9-4AA6-9571-F436FE] int) SELECT t.name, t.object_id FROM tempdb.sys.tables t JOIN tempdb.sys.columns c ON t.object_id = c.object_id WHERE c.name = '38F055D8-25D9-4AA6-9571-F436FE' 

Results Examples

 name object_id ------------------------------ ----------- #4DB4832C 1303675692 

But you will notice that the name of the object is autogenerated and has nothing to do with the name of the variable.

If you do not have a unique, unique column name that you can use to filter as described above, and the table variable has at least one row in it, you can (starting with SQL Server 2008) use %%physloc%% and DBCC PAGE to determine this information. An example is below.

 DECLARE @t2 AS TABLE ( a INT) INSERT INTO @t2 VALUES (1) DECLARE @DynSQL NVARCHAR(100) SELECT TOP (1) @DynSQL = 'DBCC PAGE(2,' + CAST(file_id AS VARCHAR) + ',' + CAST( page_id AS VARCHAR) + ',1) WITH TABLERESULTS' FROM @t2 CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% ) DECLARE @DBCCPage TABLE ( [ParentObject] [VARCHAR](100) NULL, [Object] [VARCHAR](100) NULL, [Field] [VARCHAR](100) NULL, [VALUE] [VARCHAR](100) NULL ) INSERT INTO @DBCCPage EXEC (@DynSQL) SELECT VALUE AS object_id, OBJECT_NAME(VALUE, 2) AS object_name FROM @DBCCPage WHERE Field = 'Metadata: ObjectId' 

Answer 3

From books on the Internet:

A table variable behaves like a local variable. It has a clearly defined scope, which is a function, stored procedure or package in which it is declared.

Given this, it should not be necessary to look for this value at runtime because you must know it at design time.

Answer 4

For the issue of passing arbitrary lists / arrays to a SQL Server 2005 or sproc function,
least hokey, I know, is to use an XML variable. If desired, this XML variable can be a strongly typed XML type that is associated with an XML schema.

Given a list passed to the procedure / function as XML, you can extract the list into a temp table or table through "shredding". Discarding XML means converting in the opposite direction - from XML to sets of strings. (The FOR XML clause invokes string conversion to XML conversion.)

In a custom 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 @xmlInputBondIdList is an XML fragment of the expected structure, similar to the one 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 is a set of rows

Bondid

8681

8680

8684

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