Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app purpose or something that I'm missing?

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
share|improve this question
    
I am sure many have asked this question for the years since MySQL 5.0 came into existence. Since you asked this, +1 !!! –  RolandoMySQLDBA Jul 12 '11 at 21:01

2 Answers 2

up vote 2 down vote accepted

That column exists for compatibility with other databases.

Interestingly, here is information_schema.tables in PostgreSQL

postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier |
 table_schema                 | information_schema.sql_identifier |
 table_name                   | information_schema.sql_identifier |
 table_type                   | information_schema.character_data |
 self_referencing_column_name | information_schema.sql_identifier |
 reference_generation         | information_schema.character_data |
 user_defined_type_catalog    | information_schema.sql_identifier |
 user_defined_type_schema     | information_schema.sql_identifier |
 user_defined_type_name       | information_schema.sql_identifier |
 is_insertable_into           | information_schema.character_data |
 is_typed                     | information_schema.character_data |
 commit_action                | information_schema.character_data |
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1
               FROM pg_rewrite
              WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text
            ELSE NULL::text
        END::information_schema.character_data AS commit_action
   FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

In PostgreSQL, the table_catalog field actually means something. In fact, it is part of the SQL-92 standard. The catalog of a database groups related metadata for a database. See, in PostgreSQL, the information_schema is only for the database you are connected to. In MySQL, this concept is blurred and the information_schema contains all metadata for all databases, especially if you have ALL PRIVILEGES on *.* as with root@localhost.

It makes sense that it is there in MySQL but blank. The information_schema in MySQL will collapse for you if your DB user has ALL PRIVILEGES on db.* instead of *.*. In PostgreSQL, the collapse is automatic when you connect to a database schema. So, the table_catalog column is just there for MySQL to be SQL-92 compatible and nothing more.

share|improve this answer
1  
Blurred because, in terms of behavior, a MySQL database is closer to what PostgreSQL calls a schema than it is to what PostgreSQL calls a database. –  Mike Sherrill 'Cat Recall' Dec 7 '11 at 16:02

In SQL Server this column contains the database name that contains that table, like in PostgreSQL. Also the SQL92 standard specified this.

You can find here the text of the spec that says: "

     X3H2-92-154/DBL CBR-002
     21.3 Definition Schema

     21.3.8  TABLES base table

     Function

     The TABLES table contains one row for each table including views.
     It effectively contains a representation of the table descriptors.

     .......

     Description

     1) The values of TABLE_CATALOG and TABLE_SCHEMA are the catalog
        name and unqualified schema name, respectively, of the schema in
        which the table is defined.
     ....

"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.