Fully Qualified Name in GET_DDL

By default, GET_DLL function in Snowflake will return TABLE or VIEW without database and schema path. Please see example below:

CREATE OR REPLACE TABLE GET_DDL_TEST (
    c1 VARCHAR,
    c2 INT
);
SELECT GET_DDL('TABLE', 'ERICLIN.TEST.GET_DDL_TEST');

The result is below:

create or replace TABLE GET_DDL_TEST (
	C1 VARCHAR(16777216),
	C2 NUMBER(38,0)
);

You can see that the table name GET_DDL_TEST returned without Database and Schema info. This could be problematic if user is relying on the output to re-create tables or views, and the newly created tables or views might end up in the wrong database or schema, depending on the context user was on at the time when they ran the query.

GET_DDL function does provide the 3rd optional parameter to force it to return the missing info, it is FALSE by default. And it is documented in the official documentation:

GET_DDL Arguments

So, change the query as below:

SELECT GET_DDL('TABLE', 'ERICLIN.TEST.GET_DDL_TEST', true);

-- output below
create or replace TABLE ERICLIN.TEST.GET_DDL_TEST (
	C1 VARCHAR(16777216),
	C2 NUMBER(38,0)
);

However, it is a bit cumbersome to pass this extra parameter if you always need the fully qualified path for the table or view in question. The better approach I think is to create your own function to have the 3rd parameter as TRUE by default. Example as below:

CREATE OR REPLACE FUNCTION GET_DDL_FQN(type VARCHAR, name VARCHAR)
RETURNS STRING
as
$$
  SELECT GET_DDL(type, name, true)
$$;

The we can simply use this function with the 3rd parameter to the the desired result:

SELECT GET_DDL_FQN('TABLE', 'ERICLIN.TEST.GET_DDL_TEST');

-- output as below:
create or replace TABLE ERICLIN.TEST.GET_DDL_TEST (
	C1 VARCHAR(16777216),
	C2 NUMBER(38,0)
);