kathija
0
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.
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:
