show variables where variable_name like "skip_show_database"; Variable_name Value skip_show_database OFF grant select, update, execute on test.* to mysqltest_2@localhost; grant select, update on test.* to mysqltest_1@localhost; select * from information_schema.SCHEMATA where schema_name > 'm'; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME SQL_PATH NULL mysql latin1 NULL NULL test latin1 NULL select schema_name from information_schema.schemata; schema_name information_schema mysql test show databases like 't%'; Database (t%) test show databases; Database information_schema mysql test show databases where `database` = 't%'; Database create database testtets; create table testtets.t1(a int, b VARCHAR(30), KEY string_data (b)); create table test.t2(a int); create table t3(a int, KEY a_data (a)); create table testtets.t4(a int); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; c SCHEMATA TABLES COLUMNS CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY ROUTINES STATISTICS VIEWS USER_PRIVILEGES SCHEMA_PRIVILEGES TABLE_PRIVILEGES COLUMN_PRIVILEGES TABLE_CONSTRAINTS KEY_COLUMN_USAGE columns_priv db func help_category help_keyword help_relation help_topic host proc procs_priv tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user t2 t3 v1 t1 t4 select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second time_zone_name time_zone_name time_zone_transition time_zone_transition time_zone_transition_type time_zone_transition_type t2 t2 t3 t3 t1 t1 t4 t4 select c, v2.table_name from v1 right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second time_zone_name time_zone_name time_zone_transition time_zone_transition time_zone_transition_type time_zone_transition_type t2 t2 t3 t3 t1 t1 t4 t4 select table_name from information_schema.TABLES where table_schema = "testtets" and table_name like "t%"; table_name t1 t4 select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT NULL testtets t1 1 testtets string_data 1 b A NULL NULL NULL YES BTREE show keys from t3 where Key_name = "a_data"; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t3 1 a_data 1 a A NULL NULL NULL YES BTREE show tables like 't%'; Tables_in_test (t%) t2 t3 show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t2 MyISAM 9 Fixed 0 0 0 21474836479 1024 0 NULL # # NULL latin1_swedish_ci NULL t3 MyISAM 9 Fixed 0 0 0 21474836479 1024 0 NULL # # NULL latin1_swedish_ci NULL v1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL # # NULL NULL NULL NULL view show full columns from t3 like "a%"; Field Type Collation Null Key Default Extra Privileges Comment a int(11) NULL YES MUL NULL select,insert,update,references show full columns from mysql.db like "Insert%"; Field Type Collation Null Key Default Extra Privileges Comment Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references show full columns from v1; Field Type Collation Null Key Default Extra Privileges Comment c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT NULL testtets t1 a 1 NULL YES int 11 11 11 0 NULL NULL int(11) select,insert,update,references show columns from testtets.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL drop view v1; drop tables testtets.t4, testtets.t1, t2, t3; drop database testtets; select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'latin1%'; CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN latin1 latin1_swedish_ci ISO 8859-1 West European 1 SHOW CHARACTER SET LIKE 'latin1%'; Charset Description Default collation Maxlen latin1 ISO 8859-1 West European latin1_swedish_ci 1 SHOW CHARACTER SET WHERE charset like 'latin1%'; Charset Description Default collation Maxlen latin1 ISO 8859-1 West European latin1_swedish_ci 1 select * from information_schema.COLLATIONS where COLLATION_NAME like 'latin1%'; COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 SHOW COLLATION LIKE 'latin1%'; Collation Charset Id Default Compiled Sortlen latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 SHOW COLLATION WHERE collation like 'latin1%'; Collation Charset Id Default Compiled Sortlen latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where COLLATION_NAME like 'latin1%'; COLLATION_NAME CHARACTER_SET_NAME latin1_german1_ci latin1 latin1_swedish_ci latin1 latin1_danish_ci latin1 latin1_german2_ci latin1 latin1_bin latin1 latin1_general_ci latin1 latin1_general_cs latin1 latin1_spanish_ci latin1 create function sub1(i int) returns int return i+1; create procedure sel2() begin select * from t1; select * from t2; end| select parameter_style, sql_data_access, dtd_identifier from information_schema.routines; parameter_style sql_data_access dtd_identifier SQL CONTAINS SQL NULL SQL CONTAINS SQL int(11) show procedure status; Db Name Type Definer Modified Created Security_type Comment test sel2 PROCEDURE root@localhost # # DEFINER show function status; Db Name Type Definer Modified Created Security_type Comment test sub1 FUNCTION root@localhost # # DEFINER select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; ROUTINE_NAME sel2 sub1 explain select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE # ALL NULL NULL NULL NULL 2 1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8); ROUTINE_NAME name sub1 sub1 sel2 sel2 select count(*) from information_schema.ROUTINES; count(*) 2 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION show create function sub1; ERROR 42000: FUNCTION sub1 does not exist select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 grant all privileges on test.* to mysqltest_1@localhost; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 create function sub2(i int) returns int return i+1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 sub2 return i+1 show create procedure sel2; Procedure sql_mode Create Procedure sel2 show create function sub1; Function sql_mode Create Function sub1 show create function sub2; Function sql_mode Create Function sub2 CREATE FUNCTION `test`.`sub2`(i int) RETURNS int(11) return i+1 drop function sub2; show create procedure sel2; Procedure sql_mode Create Procedure sel2 CREATE PROCEDURE `test`.`sel2`() begin select * from t1; select * from t2; end create view v0 (c) as select schema_name from information_schema.schemata; select * from v0; c information_schema mysql test explain select * from v0; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY # ALL NULL NULL NULL NULL 2 create view v1 (c) as select table_name from information_schema.tables where table_name="v1"; select * from v1; c v1 create view v2 (c) as select column_name from information_schema.columns where table_name="v2"; select * from v2; c c create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets where CHARACTER_SET_NAME like "latin1%"; select * from v3; c latin1 create view v4 (c) as select COLLATION_NAME from information_schema.collations where COLLATION_NAME like "latin1%"; select * from v4; c latin1_german1_ci latin1_swedish_ci latin1_danish_ci latin1_german2_ci latin1_bin latin1_general_ci latin1_general_cs latin1_spanish_ci show keys from v4; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment select * from information_schema.views where TABLE_NAME like "v%"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE NULL test v0 select sql_no_cache `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO NULL test v1 select sql_no_cache `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1') NONE NO NULL test v2 select sql_no_cache `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2') NONE NO NULL test v3 select sql_no_cache `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like _utf8'latin1%') NONE NO NULL test v4 select sql_no_cache `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%') NONE NO drop view v0, v1, v2, v3, v4; create table t1 (a int); grant select,update,insert on t1 to mysqltest_1@localhost; grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; grant all on test.* to mysqltest_1@localhost with grant option; select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL USAGE NO select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test SELECT YES 'mysqltest_1'@'localhost' NULL test INSERT YES 'mysqltest_1'@'localhost' NULL test UPDATE YES 'mysqltest_1'@'localhost' NULL test DELETE YES 'mysqltest_1'@'localhost' NULL test CREATE YES 'mysqltest_1'@'localhost' NULL test DROP YES 'mysqltest_1'@'localhost' NULL test REFERENCES YES 'mysqltest_1'@'localhost' NULL test INDEX YES 'mysqltest_1'@'localhost' NULL test ALTER YES 'mysqltest_1'@'localhost' NULL test CREATE TEMPORARY TABLES YES 'mysqltest_1'@'localhost' NULL test LOCK TABLES YES 'mysqltest_1'@'localhost' NULL test EXECUTE YES 'mysqltest_1'@'localhost' NULL test CREATE VIEW YES 'mysqltest_1'@'localhost' NULL test SHOW VIEW YES 'mysqltest_1'@'localhost' NULL test CREATE ROUTINE YES 'mysqltest_1'@'localhost' NULL test ALTER ROUTINE YES select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test t1 SELECT NO 'mysqltest_1'@'localhost' NULL test t1 INSERT NO 'mysqltest_1'@'localhost' NULL test t1 UPDATE NO select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test t1 a SELECT NO 'mysqltest_1'@'localhost' NULL test t1 a INSERT NO 'mysqltest_1'@'localhost' NULL test t1 a UPDATE NO 'mysqltest_1'@'localhost' NULL test t1 a REFERENCES NO delete from mysql.user where user='mysqltest_1' or user='mysqltest_2'; delete from mysql.db where user='mysqltest_1' or user='mysqltest_2'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; drop table t1; create table t1 (a int null, primary key(a)); alter table t1 add constraint constraint_1 unique (a); alter table t1 add constraint unique key_1(a); alter table t1 add constraint constraint_2 unique key_2(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`), UNIQUE KEY `constraint_1` (`a`), UNIQUE KEY `key_1` (`a`), UNIQUE KEY `key_2` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test PRIMARY test t1 PRIMARY KEY NULL test constraint_1 test t1 UNIQUE NULL test key_1 test t1 UNIQUE NULL test key_2 test t1 UNIQUE select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT NULL test PRIMARY NULL test t1 a 1 NULL NULL test constraint_1 NULL test t1 a 1 NULL NULL test key_1 NULL test t1 a 1 NULL NULL test key_2 NULL test t1 a 1 NULL select table_name from information_schema.TABLES where table_schema like "test%"; table_name t1 select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; table_name column_name t1 a select ROUTINE_NAME from information_schema.ROUTINES; ROUTINE_NAME sel2 sub1 delete from mysql.user where user='mysqltest_1'; drop table t1; drop procedure sel2; drop function sub1; create table t1(a int); create view v1 (c) as select a from t1 with check option; create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; select * from information_schema.views; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE NULL test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES NULL test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES NULL test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES grant select (a) on test.t1 to joe@localhost with grant option; select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'joe'@'localhost' NULL test t1 a SELECT YES select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE drop view v1, v2, v3; drop table t1; delete from mysql.user where user='joe'; delete from mysql.db where user='joe'; delete from mysql.tables_priv where user='joe'; delete from mysql.columns_priv where user='joe'; flush privileges; create table t1 (a int not null auto_increment,b int, primary key (a)); insert into t1 values (1,1),(NULL,3),(NULL,4); select AUTO_INCREMENT from information_schema.tables where table_name = 't1'; AUTO_INCREMENT 4 drop table t1; create table t1 (s1 int); insert into t1 values (0),(9),(0); select s1 from t1 where s1 in (select version from information_schema.tables) union select version from information_schema.tables; s1 0 9 10 drop table t1; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table character_sets CREATE TEMPORARY TABLE `character_sets` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1818 set names latin2; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table character_sets CREATE TEMPORARY TABLE `character_sets` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1818 set names latin1; create table t1 select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like "latin1"; select * from t1; CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN latin1 latin1_swedish_ci ISO 8859-1 West European 1 alter table t1 default character set utf8; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 drop table t1; create view v1 as select * from information_schema.TABLES; drop view v1; create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), i DOUBLE); select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name= 't1'; COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE a decimal(5,3) 7 7 5 3 b decimal(5,1) 7 7 5 1 c float(5,2) 5 5 5 2 d decimal(6,4) 8 8 6 4 e float 12 12 12 NULL f decimal(6,3) 8 8 6 3 g int(11) 11 11 11 0 h double(10,3) 10 10 10 3 i double 22 22 22 NULL drop table t1; create table t115 as select table_name, column_name, column_type from information_schema.columns where table_name = 'proc'; select * from t115; table_name column_name column_type proc db char(64) proc name char(64) proc type enum('FUNCTION','PROCEDURE') proc specific_name char(64) proc language enum('SQL') proc sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') proc is_deterministic enum('YES','NO') proc security_type enum('INVOKER','DEFINER') proc param_list blob proc returns char(64) proc body blob proc definer char(77) proc created timestamp proc modified timestamp proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') proc comment char(64) drop table t115; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// call p108()// ERROR 24000: Cursor is already open drop procedure p108; create view v1 as select A1.table_name from information_schema.TABLES A1 where table_name= "user"; select * from v1; table_name user drop view v1; create view vo as select 'a' union select 'a'; show index from vo; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE SCHEMATA TEMPORARY MEMORY TABLES TEMPORARY MEMORY show tables from information_schema like "T%"; Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS create database information_schema; ERROR HY000: Can't create database 'information_schema'; database exists use information_schema; show full tables like "T%"; Tables_in_information_schema (T%) Table_type TABLES TEMPORARY TABLE_PRIVILEGES TEMPORARY TABLE_CONSTRAINTS TEMPORARY create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; show tables; Tables_in_test use information_schema; show tables like "T%"; Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS select table_name from tables where table_name='user'; table_name user select column_name, privileges from columns where table_name='user' and column_name like '%o%'; column_name privileges Host select,insert,update,references Password select,insert,update,references Drop_priv select,insert,update,references Reload_priv select,insert,update,references Shutdown_priv select,insert,update,references Process_priv select,insert,update,references Show_db_priv select,insert,update,references Lock_tables_priv select,insert,update,references Show_view_priv select,insert,update,references Create_routine_priv select,insert,update,references Alter_routine_priv select,insert,update,references max_questions select,insert,update,references max_connections select,insert,update,references max_user_connections select,insert,update,references use test; create function sub1(i int) returns int return i+1; create table t1(f1 int); create view t2 (c) as select f1 from t1; create view t3 (c) as select sub1(1); create table t4(f1 int, KEY f1_key (f1)); drop table t1; drop function sub1; select table_name from information_schema.views where table_schema='test'; table_name Warnings: Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) select table_name from information_schema.views where table_schema='test'; table_name Warnings: Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) select column_name from information_schema.columns where table_schema='test'; column_name f1 Warnings: Warning 1356 View 'test.t2' references invalid table(s) or column(s) or function(s) Warning 1356 View 'test.t3' references invalid table(s) or column(s) or function(s) select index_name from information_schema.statistics where table_schema='test'; index_name f1_key select constraint_name from information_schema.table_constraints where table_schema='test'; constraint_name drop view t2; drop view t3; drop table t4; select * from information_schema.table_names; ERROR 42S02: Unknown table 'table_names' in information_schema select column_type from information_schema.columns where table_schema="information_schema" and table_name="COLUMNS" and (column_name="character_set_name" or column_name="collation_name"); column_type varchar(64) varchar(64) select TABLE_ROWS from information_schema.tables where table_schema="information_schema" and table_name="COLUMNS"; TABLE_ROWS NULL select table_type from information_schema.tables where table_schema="mysql" and table_name="user"; table_type BASE TABLE show open tables where `table` like "user"; Database Table In_use Name_locked mysql user 0 0 show status where variable_name like "%database%"; Variable_name Value Com_show_databases 0 show variables where variable_name like "skip_show_databas"; Variable_name Value show global status like "Threads_running"; Variable_name Value Threads_running 1 create table t1(f1 int); create table t2(f2 int); create view v1 as select * from t1, t2; set @got_val= (select count(*) from information_schema.columns); drop view v1; drop table t1, t2; CREATE TABLE t_crashme ( f1 BIGINT); CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from information_schema.columns where data_type = 'longtext'; table_schema table_name column_name information_schema COLUMNS COLUMN_TYPE information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; table_name column_name data_type TABLES CREATE_TIME datetime TABLES UPDATE_TIME datetime TABLES CHECK_TIME datetime ROUTINES CREATED datetime ROUTINES LAST_ALTERED datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); COUNT(*) 0