Skip to content

list tables from mysql dump file with sed

Oleksandr IY

I have situation where I need to extract tables list from mysql dump file. I tried that with sed

 sed  's/DROP TABLE.*?`(.*?)`/1/' baza.sql > 1.txt

but got this error

sed: -e expression #1, char 26: invalid reference 1 on `s' command's RHS

what did I miss in my regexp?

Advertisement

Answer

Since you are using Linux, and you need to get all strings inside the first backticks after DROP TABLE, I’d suggest using grep with a PCRE regex like

grep -oP 'DROP TABLE.*?`K[^`]+' baza.sql > 1.txt

See the PCRE regex demo.

Details

  • DROP TABLE – some literal text
  • .*? – any zero or more chars other than line break chars as few as possible
  • ` – a backtick
  • K – match reset operator discarding all text matched so far
  • [^`]+ – one or more chars other than a backtick

If you have matches once per line, you may still go with sed:

sed -nE 's/DROP TABLE.*`([^`]*)`.*/1/p' baza.sql > 1.txt

Here, -n suppresses default line output, -E enables POSIX ERE (where (...) define a capturing group, not (...)), then p flag only prints the result of the substitution.