返回一个表及其名称的存储过程 [英] Stored procedure to return a table with its name

问题描述

我可以在存储过程中返回带有其名称的表吗?

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

推荐答案

正如@gbn 所说，无法查询"使用了哪些表名.您可以将它们作为常量返回.但是表名仍然返回给客户端.我不能肯定地对每种类型的客户都这样说，但这里有一个使用 ADO 的示例.

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.

这是示例设置.

create table Tab1(Tab1ID int, Name varchar(10))
create table Tab2(Tab2ID int, Name varchar(10), Tab1ID int)

insert into Tab1 values(1, 'Name1')
insert into Tab2 values(1, 'Name2', 1)
insert into Tab2 values(2, 'Name2', 1)

查询

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

结果

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

在这种情况下，使用了两个表.查看实际返回给客户端的内容(使用 ADO)，您将看到这一点(转换为 xml).

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

<xml xmlns: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:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
        <s:AttributeType name='Tab1ID' rs:number='1' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='zz'
             rs:basetable='Tab1' rs:basecolumn='Tab1ID'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='Name' rs:number='2' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='zz' rs:basetable='Tab1'
             rs:basecolumn='Name'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10'/>
        </s:AttributeType>
        <s:AttributeType name='Tab2ID' rs:number='3' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='zz'
             rs:basetable='Tab2' rs:basecolumn='Tab2ID'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='c3' rs:name='Name' rs:number='4' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='zz'
             rs:basetable='Tab2' rs:basecolumn='Name'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10'/>
        </s:AttributeType>
        <s:AttributeType name='c4' rs:name='Tab1ID' rs:number='5' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='zz'
             rs:basetable='Tab2' rs:basecolumn='Tab1ID'>
            <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <z:row Tab1ID='1' Name='Name1' Tab2ID='1' c3='Name2' c4='1'/>
    <z:row Tab1ID='1' Name='Name1' Tab2ID='2' c3='Name2' c4='1'/>
</rs:data>
</xml>

数据在 rs:data 标签中返回.在 s:Schema 中，您可以找到返回的所有列以及有关该列的一些元数据，包括包含您正在查询的表的名称的 rs:basetable.您可以使用 记录集对象获取结果集中每一列的基表名称.

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.

这篇关于返回一个表及其名称的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！