Accessing Kdb+ system catalog

Community Support
1

Received: by 10.150.154.5 with SMTP id b5mr1252157ybe.14.1232494159355; Tue, 20 Jan 2009 15:29:19 -0800 (PST)Date: Tue, 20 Jan 2009 15:29:19 -0800 (PST)X-IP: 67.82.206.72User-Agent: G2/1.0X-Google-Token: Dm133gwAAAAZS5KQlgDWjp0q83FeylgzX-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)Message-ID: <7b68722f-14db-412e-b42c-e27dfd80061d@b38g2000prf.googlegroups.com>Subject: Accessing Kdb+ system catalogFrom: DB Guy To: “Kdb+ Personal Developers” X-Google-Approved: charlie@kx.com via web at 2009-01-21 07:23:47Hi,I am trying to access the system catalog of the kdb+ DB using SQL.Can it be done?The generic syntax would look something like this, but obviously thissyntax needs to beI need a statement to get a list of all tables in the DB / tablespace: SELECT DISTINCT DATABASENAME NAME_SPACE, TABLE_NAME TAB_NAME FROMTABLES_CATALOG_TABLEAnd I need a statement to get a list of all column names from the DB /tablespace and details:SELECT DISTINCT DATABASENAME NAME_SPACE, TABLE_NAME TAB_NAME,COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, DATA_LENGTH DATA_LEN,DATA_PRECISION DATA_PREC, DATA_SCALE DATA_SCALE FromCOLUMNS_CATALOG_TABLE Where TABLE_NAME IN (#TABLE_LIST#) Order by1,2,3Thank you in advance,DB Guy

2

tables is all tables (in root namespace), views is all views (ditto), and cols[t] is the columns of t

3

Thank you, Aaron, but I’m not sure yet whether this is quitesufficient for me.Would you expect the following statement to work? SELECT DISTINCT DATABASENAME, TABLE_NAME FROM tablesHow about this one? SELECT DISTINCT DATABASENAME, TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH,DATA_PRECISION, DATA_SCALE From cols[tables] COLUMNS_CATALOG_TABLE WhereTABLE_NAME IN (Table1, Table2, Table3)Thanks so much again.DB Guy

4

i’m not really clear what you’re trying to do

most administrative stuff in q isn’t done in (q)sql, just straight q (working with the results of “meta” is the main exception)

the result of the “tables” or “cols” commands are (symbol) lists, not tables, and can’t be selected from

(i don’t know for sure whether any of your examples are implemented in the “s)” sql implementation available in q, but i rather doubt it)

5

I want the results of each query to be capable of being delivered as atable via a SQL command sent through ODBC. It sounds like this is notpossible today, and this is what I needed to know. Thank you for yourhelp!DB Guy

6

On Fri, Jan 23, 2009 at 12:47 PM, DB Guy <abiegs@gmail.com> wrote:

> I want the results of each query to be capable of being delivered as a
> table via a SQL command sent through ODBC.  It sounds like this is not
> possible today, and this is what I needed to know.  Thank you for your
> help!

you can embed the results in a table if you need, though a lot of the concepts i think you’re referencing in your SQL don’t really apply to q (database name, data length, data precision, data scale, etc.)

if you give an example of the sort of result you want, it shouldn’t be too hard to construct an equivalent in q, modulo the above disclaimer

e.g. here’s me defining a couple tables, then printing a table of table names:

q)t:(a:1 2)
q)tt:(b:1 2)
q)tables
s#t`tt
q)(table:tables)
table

t
tt

and i used to use this as a quick summary of the tables in a given q process:

q)([table:tables]rows:count each get each tables)

table rows
t    2
tt   2