0

I've SQL files on my database server which is a unix box. I am trying to find out what are the *.sql files which use a particular column of a table. For eg the content of my_dir/SALES_TERRITORY.sql is

Select segment1,segment2
from HZ_CUST_SITE_USES_ALL
where territory_id = :p_territory_id;

So , my requirement is when I run my command I should get all the files which have the combination of 'HZ_CUST_SITE_USES_ALL' and 'territory_id'.

I did below by searching the internet but I am not getting the desired output.

grep -i "HZ_CUST_SITE_USES_ALL\|TERRITORY_ID" *

My expectation is that the above command will give me a result like this

grep -i "HZ_CUST_SITE_USES_ALL\|TERRITORY_ID" my_dir/*

SALES_TERRITORY.sql

Any help is much appreciated.

Regards,

schrodingerscatcuriosity
  • 12,396
Praveen
  • 3
  • This sounds like https://unix.stackexchange.com/questions/475908/how-can-i-find-all-lines-containing-two-specified-words --> https://unix.stackexchange.com/questions/55359/how-to-run-grep-with-multiple-and-patterns – Jeff Schaller Apr 21 '20 at 17:42

3 Answers3

0

Use grep as below to list only filenames ( -l ): ( "-maxdepth 0" is to check on only under my_dir and it will not check sub directories in it.If you want sub-directories also to be checked , just remove that )

If your filenames having spaces in that you can use as below:

find ./* -maxdepth 0 -type f -exec grep -qi "HZ_CUST_SITE_USES_ALL" {} \; -and -exec grep -li "TERRITORY_ID" {} \;

Edited for Solaris:

find ./* -prune -type f -exec /usr/xpg4/bin/grep -qi "HZ_CUST_SITE_USES_ALL" {} \; -a -exec /usr/xpg4/bin/grep -li "TERRITORY_ID" {} \;

If you are sure that you do NOT have any spaces in between filenames , you can use simple for loop as below : 

for file in $(find my_dir/* -maxdepth 0 -type f)
do
  grep -qi "HZ_CUST_SITE_USES_ALL" "$file" && grep -il "TERRITORY_ID" "$file";
done
Stalin Vignesh Kumar
  • 1,755
  • 6
  • 15
  • It'd be better, generally speaking, to use find ... -exec grep 1 ... -exec grep 2 as the command substitution will split any filenames on IFS (space, tab, newline). – Jeff Schaller Apr 21 '20 at 18:26
  • @JeffSchaller : Yeah right !!! Updated again . Thanks Jeff for pointing me things :) – Stalin Vignesh Kumar Apr 21 '20 at 18:49
  • thanks for your response , but this is giving me file names which has either HZ_CUST_SITE_USES_ALL or territory_id as string. I tested it by creating a test_file.txt . The file contains text

    'HZ_CUST_SITE_USES_ALL'
    't_ID'

    still this command is printing test_file.txt when actually it should not print it as the "territory_id" string is not present in the file.

     – Praveen Apr 21 '20 at 19:07
  • @Praveen : I tested it in that scenario also , it worked rightly for me. Have you used "-q" option in grep as i mentioned in the first grep command ? – Stalin Vignesh Kumar Apr 21 '20 at 19:13
  • if i use the first command directly i get
    find: bad option -and

    so changed it to && and ran it i get error

    grep: illegal option -- q
    Usage: grep -hblcnsviw pattern file . . .

    – Praveen Apr 21 '20 at 19:20
  • Ohh Which Linux Flavor you are using , then remove "-and" , try with "-a" instead of -and and it its still saying -a is bad option , then just give space between each -exec and try ... – Stalin Vignesh Kumar Apr 21 '20 at 19:23
  • Latest Linux distros have GNU find , which works perfectly for me here... – Stalin Vignesh Kumar Apr 21 '20 at 19:24
  • @StalinVigneshKumar please find below version

    uname -v

    Generic_150400-50

    uname -r

    5.10

     – Praveen Apr 21 '20 at 19:26
  • Ahhh , its SOLARIS Server ... – Stalin Vignesh Kumar Apr 21 '20 at 19:27
  • find my_dir/* -type f -exec grep -qi "HZ_CUST_SITE_USES_ALL" {} ; -a -exec grep -li "TERRITORY_ID" {} ;

    this command give me error

    grep: illegal option -- q Usage: grep -hblcnsviw pattern file . . .

     – Praveen Apr 21 '20 at 19:28
  • 5 mins...checking for solaris find and grep...pls – Stalin Vignesh Kumar Apr 21 '20 at 19:28
  • @StalinVigneshKumar thanks – Praveen Apr 21 '20 at 19:29
  • @Praveen : Can you try the command which i mentioned above in the answer for Solaris ...I do not have Solaris machine now ..you have to test it ...:( – Stalin Vignesh Kumar Apr 21 '20 at 19:40
  • thank you so much !! @StalinVigneshKumar , it worked like a charm ! thanks again for all your help !! – Praveen Apr 21 '20 at 19:45
  • @Praveen : Pleasure ... thanks ...Have a great day !!! – Stalin Vignesh Kumar Apr 21 '20 at 19:45
0

You can do it with below:

grep -w "HZ_CUST_SITE_USES_ALL|TERRITORY_ID" file.txt
Santosh Garole
  • 358
0

if your grep supports PCRE extension, you can use:

grep -iP '(?s)(?=.*HZ_CUST_SITE_USES_ALL)(?=.*territory_id)' infile
αғsнιη
  • 41,407