Extract DDL with comments/compress and statistics

Database
Enthusiast

Extract DDL with comments/compress and statistics

We have a requriement to extract all DDL from database and put it in a version control tool. We are using GIT and liquibase.

 

Question: Is there a way to Extract all table DDL from a database - including commentstring and compress values.

 

I am familar with the approach of using:

SELECT 'SHOW TABLE' || databasename || '.'|| tablename....... 

and then executing these queries in SQL assistant and extracting to 1 file.

 

But this does not provide the commentstring and compress values. 

 

Any thoughts on how I could get commentstring and compressvalues. Anything else that I might miss with SHOW TABLE?

 

Thanks

 


Accepted Solutions
Junior Contributor

Re: Extract DDL with comments/compress and statistics

SHOW TABLE does return COMPRESS info.

 

To get the actual comment on a table: COMMENT ON TABLE mytable;

Similar for column comments.

 

But you probably want a COMMENT ON TABLE mytable IS 'blablabla';

 

Query the CommentColumn in both dbc.TablesV and dbc.ColumnsV and build it:

SELECT
   'COMMENT ON ' || 
   CASE TableKind 
     WHEN 'T' THEN 'TABLE' 
     ...
   END || ' ' || DatabaseName || '.' || TableName || ' IS ''' || CommentString || ''';'
FROM dbc.TablesV
WHERE ...

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Extract DDL with comments/compress and statistics

SHOW TABLE does return COMPRESS info.

 

To get the actual comment on a table: COMMENT ON TABLE mytable;

Similar for column comments.

 

But you probably want a COMMENT ON TABLE mytable IS 'blablabla';

 

Query the CommentColumn in both dbc.TablesV and dbc.ColumnsV and build it:

SELECT
   'COMMENT ON ' || 
   CASE TableKind 
     WHEN 'T' THEN 'TABLE' 
     ...
   END || ' ' || DatabaseName || '.' || TableName || ' IS ''' || CommentString || ''';'
FROM dbc.TablesV
WHERE ...

 

Enthusiast

Re: Extract DDL with comments/compress and statistics

.. thank you!!

 I feel bad.. I should have been able to figure it out on my own.