By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Get Dropped table info : table_schema information from information_schema.tables

P: 23
Hi,

I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted.
After drop table when I fire a query for table_schema from information_schema.tables it will give me result as 'amit'. I didn't understand why it is happnes.

See the below steps done.

mysql> use amit;
mysql> show tables;
+----------------+
| Tables_in_amit |
+----------------+
| clconf |
| clmstr |
| clmt14 |
| clsync |
+----------------+
6 rows in set (0.00 sec)

mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| clconf |
| clsync |
| clmt14 |
| clmstr |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
58 rows in set (0.00 sec)

************************************************** ******************
Tables are present in information_schema.tables beforte delete
************************************************** ******************
mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.01 sec)


mysql> DROP TABLE amit.CLMT14 ;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE amit.CLSync ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLMstr ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLConf ;
Query OK, 0 rows affected (0.01 sec)

mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.00 sec)


mysql> select table_schema from information_schema.tables where table_name='clsync';
Empty set (0.00 sec)

mysql> select table_schema from information_schema.tables where table_name='CLCYNC';
Empty set (0.00 sec)

************************************************** ***
I used using uppercase, lowercase letter for table_name and above u can see the difference in query. i have created table using names as - CLSync, CLConf, CLMT14, CLMstr
************************************************** ***


mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
54 rows in set (0.00 sec)

************************************************** ******************
Tables are not present in information_schema.tables after delete
************************************************** ******************
But still responding to query for table_schema.
Jul 14 '09 #1
Share this Question
Share on Google+
2 Replies


P: 60
Its working fine for me. I am not sure which version of Mysql you are using?

Thanks,
Lauren
Jul 14 '09 #2

P: 23
I am using MySQL 5.1.34.
Jul 14 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.