How to get the table name from a field in a join query with MSSQL? (mysql_field_table equivalent)

Question

I'm doing a query manager in Delphi using ADO, I need to know all fields that will be returned by a query, does no matter how complex and how much joins they will have. I want to call a function, that return to me, all fields that will be returned in a specific query, and fields information, like table what is this field from.

In mysql with php, I have the command mysql_field_table, in this command I pass the result object and the field index and this command return the table name for me.

Well, that is my dream, get the table name from a field index in a query like:

SELECT * FROM TableOne Left Join Table2 ON Table2.MasterField = Table1.KeyField

Mishnah

Answer 1

You could use a TADODataSet to fetch the Recordset, iterate the Fields collection, and get the tables/fields names like this:

for I := 0 to ADODataSet1.Recordset.Fields.Count - 1 do
begin
  TableName := ADODataSet1.Recordset.Fields[i].Properties['BASETABLENAME'].Value;
  FieldName := ADODataSet1.Recordset.Fields[i].Properties['BASECOLUMNNAME'].Value;
end;

Jacobo

You dont know how much you help me. very very very thanks... im very gratefull... you are the best!!! sorry but I dont have reputation to vote, but feel your self voted! – Mishnah

+1 didn't know this was possible. Is this property generic for all database types? – Pisciculture

@whosrdaddy, Iv'e used it with MSSQL, and MS-ACCESS. it is depended on the provider (among other things). Look at a related information: When Table Name Is Available Using ADO, RDO, DAO – Jacobo