Reputation: 189

Any equivalent for DBC.TABLES and DBC.COLUMNS of Teradata in Snowflake?

In Teradata we right now have a query which gives the list of table names and column names for specific databases like:

select * from dbc.tables where databasename in ('A','B','C')

Is there an equivalent for this Snowflake?

Upvotes: 0

Answers (3)

Reputation: 175706

The closest equivalent of:

select * from dbc.tables where databasename in ('A','B','C')

is:

SELECT * 
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE DELETED IS NULL
  AND TABLE_CATALOG IN ('A', 'B', 'C')

Account_usage contains dropped objects so to get only current one filter condition DELETED IS NULL was added.

It is also worth noting that ACCOUNT_USAGE views have latency (for columns and tables up to 90 minutes).

Upvotes: 0

Reputation: 1510

INFORMATION_SCHEMA is under each database, if you want to get tables across multiple databases, then you can use

ACCOUNT_USAGE.TABLES ACCOUNT_USAGE.COLUMS

Upvotes: 1

Reputation: 183

Yes, You can use INFORMATION_SCHEMA.TABLES and COLUMNS views which will display table and column information.

Please review the below documentation for more information.

Upvotes: 0