SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB Schema for 900+ tables

    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
    Code:
    desc (SHOW tables);
    it gives me an error

    If I use
    Code:
    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?
    http://kkonline.org - Inspiring Life...

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you need to query the information_schema database.

  3. #3
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    http://kkonline.org - Inspiring Life...

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


    Table Name: stocks

    Schema: extracted using DESC stocks

    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



    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
    http://kkonline.org - Inspiring Life...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Location
    Ireland
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    http://www.hosterstats.com
    Domain Hosting History and Domain Statistics.
    http://www.hosterstats.com/blog
    HosterStats.com Blog - Knowledge From Numbers

  7. #7
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    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...

  8. #8
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.c...idator.inc.php
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •