Unix – Working with SQL files

Q01 How will you go about to list all the table names used in a number of sql scripts for a given database say MY_DB_1?
A01 Let’s say you have a number of sample sql scripts like

test.sql:

test2.sql:

Output_table_names_used_in_scripts.sh

Output.txt

Q02 How will you go about replacing the database name MY_DB_1 in the *.sql above to use MY_NEW_DEV_DB ?
A02 Let’s say that .sql files are in a test2 folder.

Analyse

Run the below command to list file names & replaced values from “MY_DB_1” to “MY_NEW_DEV_DB” without modifying the file.

The sed command uses the below format.

Modify the file with -i option

cut -d ‘:’ -f 1 will extract the file names from “test2/test.sql:FROM MY_NEW_DEV_DB.Table_A;” by using “:” as delimiter to split on.

uniq will get the unique values as file names.

xargs converts input from standard input into arguments to a command. Here it will supply the file names to the sed command.

-i replaces the text in the original file itself.

Q03 Given an SQL file used for testing as shown below, how will you delete lines that use tables MY_PROD_DB.TABLE_D & MY_PROD_DB.TABLE_X?

Test3.sql:

A03

-E is for extended regular expression.

Output:

Q. What if you want to delete all lines except the ones have MY_PROD_DB.TABLE_D & MY_PROD_DB.TABLE_X?

A. Use !d instead of d.

Output:


300+ Java & Big Data Interview FAQs

16+ Java Key Areas Interview Q&As

800+ Java Interview Q&As

300+ Java & Big Data Tutorials

Top