A system.table_settings is wanted

[ardenwick]

I want to write:

SELECT name,value FROM system.table_settings WHERE database='db' AND table='the_table' AND name='parts_to_throw_insert'

Instead of

WITH
    'parts_to_throw_insert' AS name,
    regexpExtract(create_table_query, 'SETTINGS .+', 0) AS settings_str,
    arrayMap(x -> splitByString(' = ', x), extractAll(settings_str, '([a-z_]+ = [^ ,]+)')) AS settings
SELECT
    arrayFirst(s -> s[1] = name, settings)[2] AS value
FROM system.tables
WHERE (database = 'db') AND (table = 'the_table')

[canhld94]

May be add a columns to system.tables?

[ardenwick]

CREATE OR REPLACE TABLE system.table_settings
ENGINE = Memory AS
WITH
    regexpExtract(create_table_query, ' SETTINGS (.+)', 1) AS settings_str,
    arrayMap(x -> splitByString(' = ', x), extractAll(settings_str, '([a-z_]+ = [^ ,]+)')) AS settings,
    arrayJoin(settings) AS setting
SELECT
    database,
    table,
    setting[1] AS name,
    setting[2] AS value
FROM system.tables
WHERE database NOT IN ('system', 'metric', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY
    database ASC,
    table ASC,
    name ASC