2

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?

Tom Juergens
  • 4,492
  • 3
  • 35
  • 32
  • I see now that MS SQL Server also supports INFORMATION_SCHEMA, but the syntax is not 100% compatible with MySQL. Catalog views are the recommended access method according to Books Online. – Tom Juergens Nov 13 '08 at 08:43

2 Answers2

6

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;
Nelson Miranda
  • 5,484
  • 5
  • 33
  • 54
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.

Powerlord
  • 87,612
  • 17
  • 125
  • 175