sqlsnowflake-cloud-data-platform

Get DDL of all tables under schema in one database in Snowflake


Can anyone tell me a query to DDL of all tables under schema in database. I know how to get a DDL of a table.

select get_ddl('table', 'ods.users');

It gives us only one table DDL. But in my I have around 40 tables. I want to get all tables DDL at a time instead of getting one by one. Is there any query available. If so please guide me.

Regards, Kathija.


Solution

  • GET_DDL can script entire schema(with all objects inside):

    select get_ddl('SCHEMA', 'ods');
    
    select get_ddl('SCHEMA', 'ods', true);  -- to get qualified names
    

    With Snowflake Scripting block it is possible to list over tables/views/functions/... only:

    DECLARE
    CUR CURSOR FOR SELECT CONCAT_WS('.',TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME) AS name
                   FROM INFORMATION_SCHEMA.TABLES
                   WHERE TABLE_TYPE = 'BASE TABLE'
                     AND TABLE_SCHEMA ILIKE 'ODS';
    BEGIN
      CREATE OR REPLACE TEMPORARY TABLE temp_view_defs(view_name TEXT, definition TEXT);
    
      FOR rec IN CUR DO   
        EXECUTE IMMEDIATE REPLACE('INSERT INTO temp_view_defs(view_name, definition)
                            SELECT ''<view_name>'', GET_DDL(''TABLE'', ''<view_name>'')'
                            ,'<view_name>'
                            ,rec.name);
     END FOR;
    
     LET rs RESULTSET := (SELECT * FROM temp_view_defs);
    
     RETURN TABLE(rs);
    END;
    

    For sample:

    CREATE SCHEMA ODS;
    CREATE TABLE ODS.TAB1(i INT);
    CREATE TABLE ODS.TAB2(id INT DEFAULT 0) COMMENT = 'Test';
    

    Output:

    enter image description here