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

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.

CodePudding user response：

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: