CODEWITHSUNDEEP
sqlexceloracle-sqldeveloper
Shabbir Essaji
Shabbir Essaji

Reputation: 619

How to export Table Schema with Column name and other details into Excel file from SQL Developer?

How I want my export format

I want to export these details of a table into excel file using SQL Developer.

I can only find export data & DDL scripts which the other person won't understand or not interested in looking at such scripts.

Upvotes: 3

Views: 10686

Answers (2)

akash
akash

Reputation: 829

Step 1

You can use this script to generate the values as you wish and then copy them all to an excel sheet.

SELECT tbl.NAME              AS TableName,
       col.NAME              AS ColumnName,
       typ.NAME              AS DataType,
       --,col.is_nullable as Nullable
       --,col.is_identity as PK
       
       Concat(CASE
                WHEN col.is_identity = 1 THEN 'Pk,'
                ELSE ''
              END, CASE
                     WHEN fk.constraint_column_id = 1 THEN 'Fk,'
                     ELSE ''
                   END, CASE
                          WHEN col.is_nullable = 1 THEN 'null'
                          ELSE 'not null'
                        END) AS Property
--, fk.*, col.* , typ.*
FROM   sys.columns AS col
       INNER JOIN sys.types typ
               ON typ.user_type_id = col.user_type_id
       INNER JOIN sys.tables AS tbl
               ON tbl.object_id = col.object_id
       INNER JOIN sys.schemas AS s
               ON s.schema_id = tbl.schema_id
       LEFT JOIN sys.foreign_key_columns AS fk
              ON fk.parent_object_id = col.object_id
                 AND fk.parent_column_id = col.column_id
WHERE  tbl.NAME != '__EFMigrationsHistory'
ORDER  BY tbl.NAME

Step 2 : Copying

  1. left-click on the green tick to select all.
  2. press ctrl+c to copy and then paste using ctrl+v

enter image description here

Upvotes: 2

JeromeFr
JeromeFr

Reputation: 1928

When you are on the Columns tab of a table, click on any column_name value. Press CTRL + A to select the entire table, then press CTRL + SHIFT + C to copy all the value with the header. Open a new Excel sheet and paste the content of the clipboard.

If you don't want the header, use the standard CTRL + C instead of CTRL + SHIFT + C.

Upvotes: 6

Related Questions