CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Ravish Ranjan
Ravish Ranjan

Reputation: 79

Query to get the counts of all the objects in a Snowflake database

Output be like: (Database: Snowflake)

SchemaName   TablesPerSchema ViewsPerSchema Procedures
------------ --------------- -------------- -----------
abcd        46              117            163
GDS          0               3              3
IATA         3               10             13
Integration  2               4              6

Upvotes: 1

Views: 700

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Using INFORMATION_SCHEMA views:

SELECT 
    T.TABLE_CATALOG, T.TABLE_SCHEMA
   ,COUNT_IF(T.TABLE_TYPE = 'BASE TABLE')               AS TablesPerSchema
   ,COUNT_IF(T.TABLE_TYPE = 'VIEW')                     AS ViewsPerSchema
   ,MAX(SELECT COUNT(P.PROCEDURE_NAME)
        FROM INFORMATION_SCHEMA.PROCEDURES P
        WHERE T.TABLE_CATALOG = P.PROCEDURE_CATALOG
          AND T.TABLE_SCHEMA = P.PROCEDURE_SCHEMA)      AS ProceduresPerSchema
FROM INFORMATION_SCHEMA.TABLES T
GROUP BY T.TABLE_CATALOG, T.TABLE_SCHEMA
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA;

Output:

enter image description here

Additional object types could be added(if required):

,MAX(SELECT COUNT(*)
     FROM INFORMATION_SCHEMA.FUNCTIONS F
     WHERE T.TABLE_CATALOG = F.FUNCTION_CATALOG
       AND T.TABLE_SCHEMA = F.FUNCTION_SCHEMA)      AS FunctionsPerSchema

Upvotes: 1

Related Questions