How can I get the columns / tables that a query is using during execution MySQL?

user_10298068 0
Viewed 31

I have a program that allows users to run custom queries against a number of databases. The program uses the .net connector for MySQL, which creates a connection, command and dataAdapter objects.

The objective is to get all of the tables / columns that the query is hitting. I know I could parse out the query to get these, but figured there must be an easier way using the connector.

I would also be interested in a solution that uses a query of some sort? Explain does not do it.

Example: Table1

col1 col2 col3 col4
test1 tes2 test3 test4

Query:

Select col1 as 'name', col2 as 'first' from Table1;

I am wanting to get an array of columns (not alias) that were used, in this case -> col1, col2. I have looked thru a lot of the docs and couldn't find anything like this. I am assuming there must be a metadata cache somewhere in the connection objects.

Connector Setup (Powershell):

$ConnectionString = "server=" + $MySQLHost + ";port=$Mysqlport;uid=" + $Mysqluser + ";pwd=" + $Mysqlpass + ";database=" + $Database + ";allow zero datetime=true;"

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()        
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$command.CommandTimeout = 7200;       
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.FillAsync($dataSet, "data")
$results = $DataSet.Tables[0]
powershell mysql .net
1 Answers

What are the columns used in the following queries?

SELECT COUNT(*) FROM mytable WHERE userid = 123;

SELECT col1 + col2 AS myalias FROM mytable

SELECT CURDATE();

SELECT a, b, c FROM table1
UNION
SELECT x, y, z FROM table2;

SELECT EXISTS ( SELECT * FROM mytable WHERE user_id = 123);

WITH RECURSIVE cte ( ... ) SELECT * FROM cte;

You get the idea. SQL is a powerful language, with a lot of cases that would make it hard to map the source column & table to each column of the result set.

Bill_Karwin_208600
Related