Search code examples
sqloracle-databaseddldbeaver

How to copy all column names and data types from views in DBeaver?


How to copy all column names and data types from views in DBeaver?

By right clicking on view -> generate SQL -> DDL the column names are retrieved, but data types are not retrieved.


Solution

  • Views are simply stored queries; they don't have data types as part of their definition per se, so you won't see that in the DDL. Data types are inherited from the columns in the underlying tables or derived through functions in the query at run time.

    That said, Oracle does derive and track the information you want on view columns side by side with table columns in the data dictionary.

    In DBeaver, you can select the view in the Database Navigator, right-click, and select "View View" from the menu. On the view properties pane, select the "Columns" tab, click on the column properties data and use "Ctrl+A" to select all of it. Right-click, and select "Copy Advanced Info". You can then paste into a spreadsheet or table for easy manipulation of the data to get just the info you want.

    You can get also the info you're looking for with a query, as follows:

    select column_name, data_type
      from dba_tab_cols
     where owner='OWNER'
       and table_name='VIEW_NAME'
     order by column_id;
    

    Then copy or export the result set to the clipboard or to a file.