How to list all the tables available in a current database?

Question

Question

Evgeny Shvarov · Apr 3, 2023

Hi developers!

What is the way using SQL to list all the tables available in the current database/namespace?

Product version: IRIS 2022.3

Discussion (4)1

Log in or sign up to continue

Dmitry Maslennikov · Apr 3, 2023

You can do this query

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES                   
WHERE TABLE_TYPE='BASE TABLE'

Filter by TABLE_TYPE is to get rid of system tables

1 0

score 1 · Answer 1 · 2023-04-03T04:09:04-04:00

Hi @Evgeny Shvarov,

List of Tables can be listed by using the below query against all or specific schema:

SELECT TOP 5 * FROM INFORMATION_SCHEMA.TABLES
--WHERE TABLE_NAME LIKE '%CSP%'

While Columns details can be listed by using the below query:

SELECT TOP 5 * FROM INFORMATION_SCHEMA.COLUMNS
--WHERE TABLE_NAME LIKE '%CSP%'
--AND TABLE_SCHEMA LIKE '%CSP%'

Thanks

score 0 · Answer 2 · 2023-04-04T08:46:35-04:00

Daniel Kutac · Apr 4, 2023

call %Library.SQLCatalog_SQLTables()

how about this?

0 0

score 0 · Answer 3 · 2023-04-04T09:39:45-04:00

Dmitry Maslennikov · Apr 4, 2023

This may require additional privileges

0 0