With database platforms, I often need to write queries or develop dynamic processes that use metadata tables to generate SQL to execute. However, in MemSQL, the information_schema metadata tables are highly limited, especially when it comes to MemSQL-specific attributes such as table or key types. I don’t even see a populated table that contains functions or procedures. The SHOW commands have much more information and result in records like a query, but I don’t see any way to actually use them in a query, e.g., join them with data sets or have expressions on top of them to generate SQL. Is it possible, or how else can I achieve this requirement?
1 Like
You can’t consume the output of a SHOW command from a stored procedure or from within a SQL query. I have a feature request open for that and I’ve noted your request.
You should, however, be able to execute a SHOW statement from an external client app and consume the results. If you keep your dynamic logic on the client side, that may work for you.
Also, what information are you looking for that is not available in INFORMATION_SCHEMA tables like the TABLES table and the COLUMNS table?
1 Like
Thanks for adding my request.
I’ve considered trying to use Informatica PowerCenter to work with the output of the SHOW commands or write them to tables. Still, it’d be nice to be able to do so without special tools or processes.
- I see no table with table types such as rowstore, columnstore, and/or reference. It looks like that’s being added to TABLE_STATISTICS in 7.0.
- I see no table with all indexes/keys with their type. TABLE_CONSTRAINTS only seems to contain primary keys. INDEX_STATISTICS seem to have all keys but it’s by partition without columns or type. COLUMNS.COLUMN_KEY indicates which columns are used in certain types of keys. PRI does not seem limited to primary keys (e.g., columnstore keys on columnstore tables). MUL appears to be used for the shard key and possibly unenforced unique keys. If the column is used in multiple keys, there’s no way to tell which is which. KEY_COLUMN_USAGE only appears to have primary and columnstore keys, no unenforced unique keys.
- I see no tables for functions and procedures.
Given that columnstore tables do not support primary keys, I’m trying to use the unenforced unique key to drive updates to tables. However, I’m currently unable to identify those keys using queries.
The ROUTINES table has information about UDFs and stored procedures.
The information_schema.statistics table includes all indexes. It does include unenforced unique keys; in columnstore tables you can distinguish them from the columnstore and shard keys because the index_type will be shown as BTREE (it’s not actually btree, because it is logical-only and isn’t stored at all).
Given that columnstore tables do not support primary keys, I’m trying to use the unenforced unique key to drive updates to tables. However, I’m currently unable to identify those keys using queries.
What do you mean by that? Unenforced unique keys have no semantic effect on DML - they are informational only.
1 Like
Among the other seemingly related tables, I missed that the STATISTICS table has all the indexes including their types, even CLUSTERED COLUMN which can be used to identify columnstore table types. Thanks!
To your question, I’m looking to write queries that generate update SQL based on their metadata. Since columnstore tables do not support primary keys, the best I can do is define and reference unenforced unique keys that are equivalent to the would-be primary key. (I understand that they are only informational to the system.) Otherwise, I’d be forced to document the would-be primary key elsewhere.
Makes sense, glad that solves your problem!
ROUTINES and PARAMETERS tables are in information_schema in 6.8.5. I’m not sure when they were first added.
I have the tables in 6.7, but I don’t think that they’re populated.
There is row-level security on INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS. Users have to have CREATE ROUTINE permission to see routines. That could be the reason they seem unpopulated.
Thank you. I’ll do more testing to confirm that.
FYI, I created a procedure as root. I can see it when I execute SHOW FUNCTIONS but information_schema.routines remains empty.