System views in MySQL

Question

I'm using system catalog views such as SYS.ALL_ OBJECTS, SYS.FOREIGN_KEYS etc. to get information about my database structure in MS SQL 2005.

Are there equivalent functions/views for MySQL (v. 5) servers?

Answer 1

Hmm, I haven't studied everything it contains, but there's a good deal of information in the information_schema. There's also the show command.

Still, I don't see a single command to show all the foreign keys in there.

Answer 2

For the schema (All objects);

SELECT * FROM information_schema.SCHEMATA S;

For constraints and foreign keys also;

SELECT * FROM information_schema.TABLE_CONSTRAINTS T;

For everything else check this queries;

SELECT * FROM information_schema.CHARACTER_SETS C;
SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY C;
SELECT * FROM information_schema.COLLATIONS C;
SELECT * FROM information_schema.COLUMN_PRIVILEGES C;
SELECT * FROM information_schema.`COLUMNS` C;
SELECT * FROM information_schema.KEY_COLUMN_USAGE K;
SELECT * FROM information_schema.PROFILING P;
SELECT * FROM information_schema.ROUTINES R;
SELECT * FROM information_schema.SCHEMA_PRIVILEGES S;  
SELECT * FROM information_schema.STATISTICS S;
SELECT * FROM information_schema.TABLE_PRIVILEGES T;
SELECT * FROM information_schema.`TABLES` T;
SELECT * FROM information_schema.TRIGGERS T;
SELECT * FROM information_schema.USER_PRIVILEGES U;
SELECT * FROM information_schema.VIEWS V;