Parsing SQL to Get the Tables and Columns You Actually Need
If you've ever tried to programmatically figure out which tables and columns a SQL query touches, you know it's not as simple as it sounds. String matching falls apart the moment someone uses an alias, and nested subqueries turn the whole thing into a mess. sql-metadata is a Python library that takes a different approach: it parses your SQL and hands back structured metadata you can actually use.
What It Does
sql-metadata is a Python package that parses SQL queries and extracts metadata from them. Under the hood, it uses sqlglot to do the actual parsing work, which means it's not relying on regex hacks or brittle string splitting.
At its core, the library gives you a Parser class. You hand it a SQL query, and it exposes a set of properties: the raw tokens, the columns used, the tables referenced, column aliases, and a normalized version of the query. It also handles the tricky parts—column alias resolution, subquery alias resolution, and table alias resolution—so that when you ask for columns, you get back fully qualified names rather than the shorthand someone wrote in the query.
It supports MySQL, PostgreSQL, SQLite, MSSQL, and Apache Hive syntax. The README notes that these backends differ substantially, but the query types sql-metadata handles should work across them.
Why It's Cool
The alias resolution is the real win here. This is the part that makes the library worth using over rolling your own. Consider a query like SELECT a.* FROM product_a.users AS a JOIN product_b.users AS b ON a.ip_address = b.ip_address. A naive parser would give you a.* and a.ip_address. sql-metadata resolves those back to product_a.users.* and product_a.users.ip_address, which is what you actually need if you're doing anything downstream with that information.
It knows where columns appear in the query. The columns_dict property returns a dictionary split into select, where, order_by, group_by, join, insert, and update sections. That contextual information matters—a column in a SELECT clause and a column in a WHERE clause serve very different purposes, and knowing which is which lets you build smarter tooling.
Column aliases get their own treatment. When you write SELECT a, (b + c - u) as alias1, custome_func(d) alias2 from aa, bb order by alias1, the columns property gives you ["a", "b", "c", "u", "d"] without the aliases cluttering things up. But you can still get the aliases separately via columns_aliases_names, and you can see what they resolve to via columns_aliases. The library also tracks which query section each alias appears in with columns_aliases_dict.
The resolution goes both ways. When you extract columns_dict for that same query, the order_by section shows ['b', 'c', 'u'] rather than just ['alias1']. The alias gets resolved to the actual columns it refers to, so you don't have to chase down the reference yourself.
There's a normalization helper. The README mentions a helper for normalization of SQL queries, which is useful if you're comparing queries or storing them in a consistent format.
It's maintained. The badges show active CI, coverage tracking, and a maintenance indicator. For a utility library like this, ongoing maintenance matters—SQL dialects evolve, and a parser that isn't kept up to date becomes a liability.
How to Try It
Getting started is a single pip install:
pip install sql-metadata
From there, the basic usage is straightforward. Here's how you'd extract columns from a query:
from sql_metadata import Parser
Parser("SELECT test, id FROM foo, bar").columns
# ['test', 'id']
And here's the alias resolution in action:
parser = Parser("SELECT a.* FROM product_a.users AS a JOIN product_b.users AS b ON a.ip_address = b.ip_address")
parser.columns
# ['product_a.users.*', 'product_a.users.ip_address', 'product_b.users.ip_address']
parser.columns_dict
# {'select': ['product_a.users.*'], 'join': ['product_a.users.ip_address', 'product_b.users.ip_address']}
If you want to poke at it without installing anything, there's an interactive demo at https://sql-app.infocruncher.com/. The repository is at https://github.com/macbre/sql-metadata, and the README points to test files like tests/test_getting_columns.py if you want to see more examples of what the parser can handle.
Final Thoughts
sql-metadata is a focused tool that does one thing well: turn a SQL string into structured metadata. It's not trying to be a full SQL analysis framework or a query optimizer—it's a parser with a clean API and sensible alias handling. If you're building anything that needs to understand SQL programmatically (data lineage tools, query auditors, migration scripts, or just a dashboard that shows which tables a query touches), this saves you from writing and maintaining your own parser. The fact that it leans on sqlglot for the heavy lifting is a smart choice—it means the parsing logic is battle-tested rather than homegrown. Worth a look if SQL introspection is on your problem list.
Follow @githubprojects for more developer tools and open source projects.