7

Is there a way of getting the output of a SHOW DATABASES or SHOW TABLES command to output to a text file, similar to how the SELECT ... INTO OUTFILE works?

The INTO OUTFILE produces a syntax error when used on a SHOW command. I am open to external commands if there is a way to produce this using a cmdline tool such as mysqldump.

Hearth
  • 383
  • 1
  • 4
  • 13

2 Answers2

17

The best way to output this would be to pipe the data to a file. For instance:

mysql -u root -e "SHOW DATABASES" > my_outfile.txt

@Velko's answer is a good answer but only if you can access the server file system. If the server is on a different system than the client, piping will be the only way to get the file on the local client system.

PressingOnAlways
  • 11,948
  • 6
  • 32
  • 59
4

You can try this:

SELECT TABLE_SCHEMA INTO OUTFILE '/tmp/stack.txt' FROM information_schema.TABLES GROUP BY TABLE_SCHEMA

Another Example:

SET @databasesInfo := '';
SHOW DATABASES WHERE (@databasesInfo := CONCAT(@databasesInfo, `Database`, ','));
SELECT @databasesInfo INTO OUTFILE '/tmp/so2.txt';
Velko Georgiev
  • 684
  • 4
  • 11
  • the second one is cool use of a variable I must say (except for the command at the end of the output file) – Drew Nov 10 '15 at 00:43
  • Both your solutions work, but the answer from @PressingOnAlways is simpler for my purposes. +1 for the concat-variable hack, very clever. – Hearth Nov 10 '15 at 03:56