Extract table name from select statement with joins

[Hassnain-Alvi]

I am using sqlparse to get table names from queries. My queries are complex and contain joins as well. Fetching table name from an insert statement is easy but with select i am getting hard time Here is my code:

	stmt, err := sqlparser.Parse(sql)
	if err != nil {
	   fmt.Println("Error: " + err.Error())
	}
	switch stmt := stmt.(type) {
	case *sqlparser.Select:
		var sel = stmt
		var tbl = sel.From[0]
		switch tblst := tbl.(type) {
		case *sqlparser.JoinTableExpr:
			var s = tblst
			var ss = s.RightExpr
			switch expr := ss.(type) {
			case *sqlparser.AliasedTableExpr:
				var cExpr = expr.Expr
				fmt.Println(reflect.TypeOf(cExpr))
				fmt.Printf("%#v\n", cExpr)
			}
		}
	case *sqlparser.Insert:
		var ins = stmt
		fmt.Println(ins.Table.Name)
	}

I am stuck after that because it returns a table indent and the fields are not exported Sorry, i am new to golang so not sure if its the right way to do this Can you please guide me to extract all tables from a complex select query with joins?

[fengbeihong]

buffer:= sqlparser.NewTrackedBuffer(nil) stmt.From.Format(buffer) fmt.Println(buffer)

[raintean]

@Hassnain-Alvi

stmt, err := sqlparser.Parse(sql)
if err != nil {
    fmt.Println("Error: " + err.Error())
}
tableNames := make([]string, 0)
_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
    switch node := node.(type) {
    case sqlparser.TableName:
        tableNames = append(tableNames, node.Name.CompliantName())
    }
    return true, nil
}, stmt)
fmt.Println(tableNames)

[prince612mittal]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

[Hassnain-Alvi]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

sure whats the problem you are facing ?

[prince612mittal]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

sure whats the problem you are facing ?

I have started today only and I am unable to get through its documentation for parsing table name from simple expression

[prince612mittal]

@Hassnain-Alvi I am not even able to use ins.Table.name reference your above mentioned code it shows error

[sananguliyev]

@Hassnain-Alvi

stmt, err := sqlparser.Parse(sql)
if err != nil {
    fmt.Println("Error: " + err.Error())
}
tableNames := make([]string, 0)
_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
    switch node := node.(type) {
    case sqlparser.TableName:
        tableNames = append(tableNames, node.Name.CompliantName())
    }
    return true, nil
}, stmt)
fmt.Println(tableNames)

@raintean thanks for the solution but it's not valid if query has alias of the join or subquery. E.g. The result of this query select * from db.my_table as mt, (select * from db2.users) as u join db3.other_table ot on ot.user_id = u.id is [my_table users other_table ot u] which is not correct.

[virgiliosanz]

@sananguliyev Have you found a solution for that?

[sananguliyev]

@virgiliosanz I have stopped working on that since more than a year. I remember I had workaround but do not remember what was that. In case I find I will let you know.

[sananguliyev]

@virgiliosanz I have found my workaround. Instead of using Walk method of the SQL parser, I switched to using the Go's reflect in a recursive function.

[virgiliosanz]

But how do you check it, because I cannot see the difference in type when it is an alias or a real table name.... Do you check the 2 or 3 previous nodes.or something liike that?

El mié, 14 ago 2024, 21:04, Sanan Guliyev @.***> escribió:

@virgiliosanz https://github.com/virgiliosanz I have found my workaround. I was using Go reflect instead of using Walk method of sql parser.

— Reply to this email directly, view it on GitHub https://github.com/xwb1989/sqlparser/issues/46#issuecomment-2289620249, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACBAVU2QENGBL2SAXI6OPLZROS4FAVCNFSM6AAAAABMP6YQE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBZGYZDAMRUHE . You are receiving this because you were mentioned.Message ID: @.***>

[sananguliyev]

But how do you check it, because I cannot see the difference in type when it is an alias or a real table name.... Do you check the 2 or 3 previous nodes.or something liike that? El mié, 14 ago 2024, 21:04, Sanan Guliyev @.> escribió: … @virgiliosanz https://github.com/virgiliosanz I have found my workaround. I was using Go reflect instead of using Walk method of sql parser. — Reply to this email directly, view it on GitHub <#46 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACBAVU2QENGBL2SAXI6OPLZROS4FAVCNFSM6AAAAABMP6YQE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBZGYZDAMRUHE . You are receiving this because you were mentioned.Message ID: @.>

@virgiliosanz yes, I was passing previous node type whether it's SimpleTableExpr if yes then next node is TableIdent I was labelling it as table name. This approach was covering all the use cases, simple, aliased and join queries.

[virgiliosanz]

Hi @sananguliyev, many thanks. I make it work. I past my code here just for future reference:

// getAffectedTables returns the affected tables for a given query.
func getSelectAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
	affectedTables := make([]string, 0)
	var previous sqlparser.SQLNode

	walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch n := node.(type) {
		case sqlparser.TableName:
			switch previous.(type) {
			case *sqlparser.AliasedTableExpr:
				table := normalizeTableName(n.Name.CompliantName())
				affectedTables = append(affectedTables, table)
				// fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
			}
			// default:
			// 	fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
		}

		previous = node
		return true, nil
	}

	sqlparser.Walk(walkLog, *stmt)

	return uniqueNonEmptyElementsOf(affectedTables), nil
}

func getAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
	affectedTables := make([]string, 0)

	walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch n := node.(type) {
		case sqlparser.TableName:
			table := normalizeTableName(n.Name.CompliantName())
			// fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
			affectedTables = append(affectedTables, table)
			// default:
			// 	fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
		}
		return true, nil
	}

	sqlparser.Walk(walkLog, *stmt)

	return uniqueNonEmptyElementsOf(affectedTables), nil
}

[sananguliyev]

Hi @sananguliyev, many thanks. I make it work. I past my code here just for future reference:

// getAffectedTables returns the affected tables for a given query.
func getSelectAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
	affectedTables := make([]string, 0)
	var previous sqlparser.SQLNode

	walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch n := node.(type) {
		case sqlparser.TableName:
			switch previous.(type) {
			case *sqlparser.AliasedTableExpr:
				table := normalizeTableName(n.Name.CompliantName())
				affectedTables = append(affectedTables, table)
				// fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
			}
			// default:
			// 	fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
		}

		previous = node
		return true, nil
	}

	sqlparser.Walk(walkLog, *stmt)

	return uniqueNonEmptyElementsOf(affectedTables), nil
}

func getAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
	affectedTables := make([]string, 0)

	walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
		switch n := node.(type) {
		case sqlparser.TableName:
			table := normalizeTableName(n.Name.CompliantName())
			// fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
			affectedTables = append(affectedTables, table)
			// default:
			// 	fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
		}
		return true, nil
	}

	sqlparser.Walk(walkLog, *stmt)

	return uniqueNonEmptyElementsOf(affectedTables), nil
}

Thanks for sharing this. That would be the another way to keep the previous node in order to only extract table names, not aliases.