12
0
During this post we will discuss how to extract the DDL of entire user defined tables in particular database. Later on, we will use these DDL to create the same tables in another Database. Very first thing comes to our mind that we can use GET_DDL command to extract the metadata. But Consider scenario if database has more than 100+ tables, then running GET_DDL on each table is not right approach. Moreover, we have to create these DDL in another database as well. Extracting the DDL manually for every table and switch to new DB is not feasible solution.
We need a programmatic approach which should traverse the Table list automatically and Switch to the new Database and create the DDL successfully. We have achieved the DDL extraction use case via Python in my previous post. But now we want this to be achieve in snowflake itself rather than moving to external tool or language. To implement the same I have used the latest feature sql scripting concepts introduced by Snowflake.
Lets see the technical implementation:
We will be extracting the DDL from source DB i.e. DEMO_DB. As per the below screenshot we could see there are 23 tables present in DB.
Process would extract the DDL and create in FINAL_DDL database. Currently database is empty.
Below Procedure created in DEMO_DB:
Executed the Procedure and got completed successfully. We have also captured the output in variable and we can refer at later stage for our validations.
Now verify the FINAL_DDL database. All 23 tables should have been created there.
