Hi all,
I am working on a project where I have 900+ tables in a existing db and I need to know the schema for the DB.
If I use
desc (SHOW tables);
it gives me an error
If I use
SELECT <TABLE_NAME1> from dba_tables;
it results in schema for only TABLE_NAME1. I need this o be done for TABLE_NAME1…900
Is this possible only using sql statements?
you need to query the information_schema database.
Hi,
Thanks for the suggestion, however I am still not sure how to use it to get the structure of all the tables in the existing DB
Basically I need complete schema of all the tables in the existing DB
Hi,
Thanks for your response.
I have the following requirements
Currently working in a db which has 900 tables and each table has a schema attached to i
For example
Table Name: columns_priv
Schema: The info which I get from DESC columns_priv
Field Type Null Key Default Extra
Host char(60) PRI
Db char(64) PRI
User char(16) PRI
Select_priv enum(‘N’,‘Y’) N
Insert_priv enum(‘N’,‘Y’) N
Update_priv enum(‘N’,‘Y’) N
Delete_priv enum(‘N’,‘Y’) N
Create_priv enum(‘N’,‘Y’) N
Drop_priv enum(‘N’,‘Y’) N
Grant_priv enum(‘N’,‘Y’) N
References_priv enum(‘N’,‘Y’) N
Index_priv enum(‘N’,‘Y’) N
Alter_priv enum(‘N’,‘Y’) N
Create_tmp_table_priv enum(‘N’,‘Y’) N
Lock_tables_priv enum(‘N’,‘Y’) N
[b]Table Name: stocks
Schema: extracted using DESC stocks
[/b]
[i] Field Type Collation Attributes Null Default Extra Action
ID int(11) No auto_increment
stock varchar(64) latin1_swedish_ci No
value varchar(16) latin1_swedish_ci No 0
changepoints varchar(16) latin1_swedish_ci No 0
open varchar(16) latin1_swedish_ci No
intra_top varchar(16) latin1_swedish_ci No
intra_down varchar(16) latin1_swedish_ci No
date varchar(10) latin1_swedish_ci No
time varchar(6) latin1_swedish_ci No
unixtime int(12) No 0
reallocaltime int(12) No 0
md5 varchar(32) latin1_swedish_ci No
[/i]
Like this I have 900 different table and want to extract the table structure for each of them using Db query.
DESC <tablename> works only for one table but my requirement is to have data for all the tables
you can either learn how to query the information_schema (it’s well documented in the mysql manual), in whch case you would run one query and produce everything you need
or else you can do it like this – first, run SHOW TABLES to get the list of tables, copy the results into a text editor, then edit it so that each line looks like SHOW CREATE TABLE tablename; and then run that
frankly, i’m pretty good at writing queries, you might think i would take the first option, but i would actually always do the second because it’s so much faster (assuming you have a decent text editor)
A nastier solution would be to run this at command line:
mysqldump databasename -uusername -ppassword -d > databasename.schema.txt
This effectively dumps the table schema into a text file without the data. I think it varies from version to version.
Regards…jmcc
Another alternative: download MySQL Workbench, and have it analyse your DB for you. It’ll show the relations between the tables too.
It can be a bit buggy at times, but it’s a darn fine piece of software.
One caveat: I have no idea how it will perform for a database of 900+ tables…
jmccormac is right. Work on the empty tables.
Further, I would build a little script to find out the schema manually. It really takes a time to analyze them all. And you cannot do it one by one.
Having 900+ tables might mean to me - A LOT OF them are probably similar tables with different contents. This is a wise guess only.
Probably customized per users’ requirements or something.
And again, a DBA might not have created them manually, nor wrote some INSERT INTO … etc. to put the data there 
So, many of them should have been scripted and semi-automatic.
Check those few scripts as well, that I use to make printable reports of the schemas (of course no graphics and relationships lines).
http://smarty-framework.googlecode.com/svn/trunk/meta/
http://smarty-framework.googlecode.com/svn/trunk/library/classes/sanjaal/class.database_structure_validator.inc.php
Just wondering, is the database a multi-blog wordpress installation as 900+ tables seems like a hell of a lot for any app.
Do you have the use of PHPMyAdmin available to you to access the database concerned? If so you can use PHPMyAdmin to print the “data dictionary” for the database. I don’t know if it would timeout however for generating the data dictionary for a database with 900+ tables