Solved

How to print out MySQL table structure and content in text format on Mac OS X

Posted on 2010-08-18
8
495 Views
Last Modified: 2012-05-10
Which MySQL editor could show my database table in the following kind of format? I'd also need to display the content in this kind of text format.

 
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| product_id  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| url         | varchar(100)        | NO   | UNI | NULL    |                |
| name        | varchar(50)         | NO   |     | NULL    |                |
| description | varchar(255)        | NO   |     | NULL    |                |
| price       | decimal(10,2)       | NO   |     | NULL    |                |
| visible     | tinyint(1) unsigned | NO   | MUL | 1       |                |
+-------------+---------------------+------+-----+---------+----------------+

Open in new window

0
Comment
Question by:leppis75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 33468733
I use the oracle SQL Developer - it's free you just have to register on the oracle site in order to download it. The registration is free and the tool is free and let's you manage just about everything regarding databases, and is extensible for just about every available database.

Here's a screen grab of the tool's view of a table structure. it's very similar to using Microsoft's Management Studio.

In my configuration I am connected to four different local Microsoft DBs - one web based MS SQL instance and 2 MySql instances - all different versions and locations and can work on all of them at the same time...

Takes a while to figure out how to configure everything but not terribly difficult the learn.
Screen-shot-2010-08-18-at-3.12.2.png
0
 

Author Comment

by:leppis75
ID: 33468834
Thanks for the tip, but can I get it to print the tables out in that kind of text table format, that I'd pasted in my question? The reason why I'm asking is, that I'd like to, after finding the right tool, open another question, where I would copy-paste content from couple of tables in my database.
0
 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 33468912
in most databases and in any tool with query capability just use this


sp_help tablename

In most tools where you can print the information shown on the screen you can just print this out
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 33468968
If that doesn't work try this as a query - some tools won't output the actual table structure
This returns a text output that you can print...

select * from information_schema.columns where table_name='tablename'
0
 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 33469125
Sorry for so many comments - I just like to check my advice and I'm discovering options while I look at your issue.
In the Oracle SQL Developer - I just use this query - select * from information_schema.columns where table_name='tablename'
Then in the results pane I right click on the resulting table and I can export the results in about ten different formats including html, xls, csv, text etc... So it should do what you're looking for.

0
 

Author Comment

by:leppis75
ID: 33470012
No, I'm sorry, but I don't think any of this is what I'm looking for. I think, that if I could make the query in my Terminal, I'd get the result I want...

I think I'll give up and just take screenshots of what I need to show. Thanks for trying to help.
0
 
LVL 3

Accepted Solution

by:
seenall earned 500 total points
ID: 33473169
Basic mechanism without PERL - use sed

1) Create a single shell script as follows (e.g. /usr/local/bin/tab2csv.sed")

s/\t/","/g;s/^/"/;s/$/"/;s/\n//g

Don't worry too much about what this is / is not doing until you really want to learn sed

2) now create a variable in your .bashrc file

TAB2CSVSED="sed -f /usr/local/bin/sedtab2csv.sed"

create a sql script (e.g. /home/me/sqlscrs/mytabint.sql)

select table_name, column_name, data_type -- etc
from information_schema.columns
where table_schema = 'mydbuser'
and table_name = 'mytableofinterest';


4) cd /home/me/sqlscrs/ # making life easier

Now - when you run a mysql command line - pipe the output to this simple edit (sed) command
create a SQL Script and catch the output to a file

e.g.
/usr/bin/mysql -u mydbuser -P3306 -h 127.0.0.1 -pmypass -D mydb -A -e "source mytabint.sql" | ${TAB2CSVSED} > mytabdet.csv

cat mytabdet.csv

With the CSV file you can grab what ever you want via a text editor or excel

Mac - being Darwin UNIX - opens up more flexibility if the Shell commands are used

Best
0
 
LVL 3

Expert Comment

by:seenall
ID: 33631563
Hello leppis75

I am really pleased you managed to get this working.  Automation makes life a little easier

All the best
seenall
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question