HANA list / show tables SQL Command

Question

How to show / list all tables in SAP HANA via SQL?

score 36 · Accepted Answer · edited Jan 23 '17 at 09:08

36

SAP HANA provides a database catalog (just like most other DBMS) via system tables like TABLES, VIEWS, USERS, etc. ... These are the ANSI-92 compliant metadata views.

Instead of the INFORMATION_SCHEMA SAP HANA provides these views in the PUBLIC schema (again, like other DBMS do that).

Example:

select * from tables
select * from public.tables

The M_TABLES provides information about the runtime objects that represent tables.

edited Jan 23 '17 at 09:08

Thorsten Niehues

13,712
22
78
113

answered Jan 18 '17 at 22:51

Lars Br.

9,949
2
15
29

1

Downvote because of what? The answer I provided is accurate and complete. – Lars Br. Jan 19 '17 at 11:40
2

TABLES and the other metadata views are VIEWS in schema SYS, and at the same time PUBLIC SYNONYMS (in schema PUBLIC). But this should be no reason at all for a downvote! Whoever downvoted should at least provide a better answer or comment. – Christoph G Jan 20 '17 at 08:07

score 2 · Answer 2 · edited Sep 05 '18 at 11:44

2

SELECT TABLE_NAME FROM SYS.M_TABLES

edited Sep 05 '18 at 11:44

AymDev

6,626
4
29
52

answered Sep 05 '18 at 07:11

Janusz

1,343
11
16

1

`M_TABLES` is a system view that provides **runtime information** on tables - it's not the primary catalog view to query for tables. For example, it does not include *virtual tables*, where `TABLES` does. – Lars Br. Feb 12 '19 at 23:26

score 0 · Answer 3 · answered Dec 12 '18 at 22:30

0

To view tables from a specific schema :

select * from SYS.M_TABLES where SCHEMA_NAME ='<your schema name goes here>'

answered Dec 12 '18 at 22:30

Alferd Nobel

3,185
2
30
35

same problem with `M_TABLES` as explained in the other answer. – Lars Br. Feb 12 '19 at 23:27

score 0 · Answer 4 · answered Jul 06 '23 at 09:27

0

This below query will list all your schema tables

select * from tables where SCHEMA_NAME ='schema_name'

answered Jul 06 '23 at 09:27

Sharan

253
4
14

snow_FFFFFF · Answer 5 · 2017-01-18T19:51:11.103

-3

It is my understanding that HANA is compatible with ANSI SQL. If this is actually the case, the following should work:

SELECT * FROM INFORMATION_SCHEMA.TABLES

Of course, I don't have access to a hana instance to prove this.

CORRECTION: After looking at some documentation, it looks like SAP supports this type of thing through a SYS schema:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/cbb10c75191014b47ba845bfe499fe/content.htm?frameset=/en/2e/1ef8

So, I think you would actually select from:

SYS.M_TABLES

edited Jan 18 '17 at 19:51

answered Jan 18 '17 at 19:44

snow_FFFFFF

3,235
17
29

M_TABLES is not providing information on all tables in the database, but runtime information on ROW- and COLUMNstore information. – Lars Br. Jan 18 '17 at 22:51

HANA list / show tables SQL Command

5 Answers5

Linked