Data Dictionary User Tables? 1

[Quehay]

I'm new to mySQL. Is there a way to get a good synoptic view of tables,cols,indexs, etc. by querying internal dictionary tables as you can in Oracle & SQL Server?

Also is there a way to script an entire database out to DDL and insert scripts?

Thanks!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

OK I found this already in a keyword search: thread436-616293

Can someone out there who knows this RDBMS well please tell me that there's more metadata available than checkboxes for privs alongside table names??? I was psyched to start working with mySQL but if this is what they call an RDBMS I'll gladly pay for SQL or Oracle licensing

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

oh...and foreign key support, triggers, views, udf's, stored procedures, cursors...OUCH!!!!!!!!!!!!!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sleipnir214]

<sarcasm>
It would be a pleasure to help someone who's so open-minded...
</sarcasm>

What more metadata about user permissions do you need?


The easy answers. MySQL does not, in current production-release software version, support triggers, stored procedures,cursors or views.


Foreign key constraints are implemented in versions of MySQL newer than 3.23.44, provided that you use the InnoDB table type.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Quehay]

<disappointment><playful mocking><genuine horror>
How can you work with a database when you can't find out what's in it (structurally) at varying angles of attack and granularity? I'm not trying to beat up on the product (mySQL), because it's obviously serving a number of people very well. But it's a disappointment to find out that this it doesn't hold up next to the real players. Saying that mySQL doesn't do views, etc. is kinda like saying "yeah well this bagel shop doesn't have cream cheese and lox"...

OK here's what I mean by metadata, with a very limited example</></></>
[tt]SQL> column type format a20
SQL> break on type skip 1
SQL> column object format a50
SQL> select
2 object_type "Type",
3 object_name "Object",
4 created
5 from
6 user_objects
7 order by 1,2
8 /
Foreign keys for Tables in MY_DATABASE
01-MAR-04

------------------------------------------------------------

Type Object CREATED
-------------------- -------------------------------------------------- ---------
INDEX PK_INS 14-JAN-04
UK_COU_INS_STA 16-JAN-04
UK_INS 15-JAN-04

PACKAGE PKG_EXCEPTIONS 03-JAN-04
PKSPEC_ERROR_HANDLING 03-JAN-04
TEST_IT 24-DEC-03

PACKAGE BODY 24-DEC-03

PROCEDURE 15-JAN-04

SEQUENCE STUDENT_ID 12-NOV-03

TABLE ATTENDANCE 12-NOV-03
AUTH 12-NOV-03
CK_LOG 14-JAN-04
COMPANY 12-NOV-03
COURSE 12-NOV-03
DEFO 14-JAN-04
ERROR_LOG 03-JAN-04
INSTRUCTOR 12-NOV-03
OFFERING 12-NOV-03
PLAN_TABLE 14-JAN-04
SITE 12-NOV-03
STUDENT 12-NOV-03
SUBJECT_AREA 12-NOV-03
TEST 14-JAN-04

VIEW BIEW_OBJECT_DEPENDENCIES 4-JAN-04

[/tt]


Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sleipnir214]

Be honest. How much of that folderol have you ever really needed? So you know that the table ATTENDANCE was created on 2003-11-12. What will that information and two bits buy you that two bits alone will not?


MySQL was not designed to be a replacement for Oracle of SQL Server. MySQL was designed to be a fast, lightweight, robust, RDBMS.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BNPMike]

I have to agree this is an unforgivable design mistake. It's so useful, surely causes no problems and remember:

Rule 4: Dynamic On-Line Catalog Based on the Relational Model

A relational database must provide access to its structure through the same tools that are used to access the data. This is usually accomplished by storing the structure definition within special system tables.

That's Mr Codd speaking.

 

[sleipnir214]

MySQL conforms completely with Codd's Rule 4. MySQL provides SQL queries to retrieve information about a database and those queries return information in exactly the same form a a SELECT query.

However, Codd doesn't specify what information should be or must be available.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Chacalinc]

Does anyonw know about SAP SB? it has all the functione that is not in MySQL, but:

http://www.mysql.com/press/release_2003_16.html

as you can see, MySQL is growing faster. As you know, V5.x has stored procedures, and others.. what news will have the net release?

Just a comment.

I like MySQL, but I know they are not trying to overcome to Oracle or others, they want to get a very fast DB.

 

[sleipnir214]

If I were looking for an RDBMS that[ul][li]was cheap[/li][li]supported triggers, user-defined functions, user-defined operators, views, cursors, sequences and other features found in big-name RDBMSes[/li][li]provides gobs and gobs of metadata[/li][/ul]I would probably give PostgreSQL 7.4.1 a try.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Quehay]

The example above was noted as "limited", but even that shows all the objects that a user in an Oracle db owns: indexes, tables, procs, etc. This is essential info.

Similarly you can query USER_TAB_COLS and get column names, datatype, default value, NULLABLE, indexed, constraints, etc. for any or all tables for user (or higher--dba).

If you are coming into a db cold (someone else's creation) you can't function properly without a view into the structure and integrity of a db. If the orders table allows a NULL value for customerID or allows the insertion of a customer ID that doesn't exist, or the Customers table allows you to delete customers for which orders exist you've got ISSUES in the making.

No use preaching to the choir or the hottentots on this one. But discovering this lack of system tables in mySQL has made me decide to NEVER consider it as a development db.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

sleipnir: Thanks for that recommendation! And for providing a spelling for 'folderol' ;-)

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sleipnir214]

Just keep in mind that MySQL will provide everything but the constraint information in the form of the DESCRIBE query.

The foreign key constraints are available through a SHOW CREATE TABLE or SHOW TABLE STATUS query.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BNPMike]

To be fair, MySQL has a lot of interesting features that maybe other dbmss don't. Having been nurtured for decades on DB2, I find MySQL just fine.

 

[Quehay]

Thanks again guys--that's what I was looking for: an advocate/experienced user's view of the product. I'll keep looking at it with an open[ed] mind!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com