Stored Procedure To Return A Table With Its Name

Can I return a table with its name in a stored procedure?

Solution 1:

No (assuming I understand YASQ)

There is no ThisTable() type function

It'd be a constant like this

SELECT 'ThisTable'AS CurrentTableName FROM ThisTable^

Solution 2:

As @gbn says, it is not possible to "query" what table names are used. You can return them as constants. But the table names are still returned to the client. I can't say that for sure for every kind of client but here is an example using ADO.

Here is the sample setup.

createtable Tab1(Tab1ID int, Name varchar(10))
createtable Tab2(Tab2ID int, Name varchar(10), Tab1ID int)

insertinto Tab1 values(1, 'Name1')
insertinto Tab2 values(1, 'Name2', 1)
insertinto Tab2 values(2, 'Name2', 1)

The query

select *
from Tab1
  inner join Tab2
    on Tab1.Tab1ID = Tab2.Tab1ID

Result

Tab1ID      Name       Tab2ID      Name       Tab1ID
----------- ---------- ----------- ---------- -----------
1           Name1      1           Name2      1
1           Name1      2           Name2      1

In this case there are two tables used. Looking at what is actually returned to the client (using ADO) you will see this (converted to xml).

<xmlxmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'xmlns:rs='urn:schemas-microsoft-com:rowset'xmlns:z='#RowsetSchema'><s:Schemaid='RowsetSchema'><s:ElementTypename='row'content='eltOnly'rs:updatable='true'><s:AttributeTypename='Tab1ID'rs:number='1'rs:nullable='true'rs:writeunknown='true'rs:basecatalog='zz'rs:basetable='Tab1'rs:basecolumn='Tab1ID'><s:datatypedt:type='int'dt:maxLength='4'rs:precision='10'rs:fixedlength='true'/></s:AttributeType><s:AttributeTypename='Name'rs:number='2'rs:nullable='true'rs:writeunknown='true'rs:basecatalog='zz'rs:basetable='Tab1'rs:basecolumn='Name'><s:datatypedt:type='string'rs:dbtype='str'dt:maxLength='10'/></s:AttributeType><s:AttributeTypename='Tab2ID'rs:number='3'rs:nullable='true'rs:writeunknown='true'rs:basecatalog='zz'rs:basetable='Tab2'rs:basecolumn='Tab2ID'><s:datatypedt:type='int'dt:maxLength='4'rs:precision='10'rs:fixedlength='true'/></s:AttributeType><s:AttributeTypename='c3'rs:name='Name'rs:number='4'rs:nullable='true'rs:writeunknown='true'rs:basecatalog='zz'rs:basetable='Tab2'rs:basecolumn='Name'><s:datatypedt:type='string'rs:dbtype='str'dt:maxLength='10'/></s:AttributeType><s:AttributeTypename='c4'rs:name='Tab1ID'rs:number='5'rs:nullable='true'rs:writeunknown='true'rs:basecatalog='zz'rs:basetable='Tab2'rs:basecolumn='Tab1ID'><s:datatypedt:type='int'dt:maxLength='4'rs:precision='10'rs:fixedlength='true'/></s:AttributeType><s:extendstype='rs:rowbase'/></s:ElementType></s:Schema><rs:data><z:rowTab1ID='1'Name='Name1'Tab2ID='1'c3='Name2'c4='1'/><z:rowTab1ID='1'Name='Name1'Tab2ID='2'c3='Name2'c4='1'/></rs:data></xml>

The data is returned in the rs:data tag. In the s:Schema you find all columns returned with some meta data about the column including the rs:basetable that contain the name of the table you are querying. You can use the Recordset Object to get the base table name for each column in the result set.

Solution 3:

Look up OUTPUT parameters and RETURN values. A stored procedure can select data from a table, assign parameter values that can be fed back to the calling procedure and referenced there by name, and can also return a value to the calling procedure.

http://support.microsoft.com/kb/262499

http://msdn.microsoft.com/en-us/library/ms188001.aspx