Database : Get SQL from a created table with/without data insertion

Suppose you have some tables in Database (Oracle, MySQL etc) . Sometimes you need the SQL queries of that tables. These SQL queries will be able to make exactly similar tables like these. You can even get the data of these tables by running sql queries. You can also get it without data (only table structure), it’s up to yours. But first of all, we need the SQL of the already generated tables. To do this, follow the steps:

Step1

Open a schema of Database using your SQL editor (PopSQL, DataGrip, Navicat etc).

Step2

Go to Tables. Then you can see the tables of your schema.

Image for post
Image for post
After following Step 1 and 2 (in Navicat)

Step3

Select the table / tables of which you want to get the SQL code. To select multiple table, press Ctrl and then click on the tables with your mouse. If you want to select all tables at a time, press Ctrl+A.

Image for post
Image for post
After selecting desired tables

Step4

After selecting, click right button of your mouse on any of the selected table/tables. You will get an option Dump SQL File. Click on this.

Image for post
Image for post

Step5

You will be provided two options — 1. ‘Structure and Data’ , 2. ‘Structure Only’.

If you select 1, then your SQL will be generated with structure of the selected table/tables and data insertion.

If you select 2, then your SQL will be generated only to get the structure of the selected table/tables.

Choose your option.

Step6

After choosing your option, your SQL file will be generated. You can give it a name and then you can save it to your desired directory.

Image for post
Image for post

Step7

Click on Close. Don’t click on Start because I don’t know what will happen after clicking on Start. I’m too lazy to make experiments.

Image for post
Image for post

Step8

Go to that directory where you saved the SQL file. The file type should be .sql and your can open it using any SQL Editor or Notepad or Notepad++ or something else…

You can run it to anywhere to make the tables. But a concern here. Look at the SQL commands, it’s like something DOT something. Wait, look at this

Image for post
Image for post

Here, it is HR.REGIONS , have you noticed??? Actually if you run this SQL anywhere (I repeat anywhere), the tables will be created there from where you collected the SQL. But we don’t want that, right? So what to do?

Edit the names. Like, use CREATE TABLE REGIONS instead of CREATE TABLE HR.REGIONS.

Step9

Your are done. You sometimes have to make some trial and errors to do these types of things. Don’t worry.

Thank your for reading this article…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store