WL#1629: SHOW with WHERE(partially) &

WL#173:  Create Data Dictionary Tables for SHOW Commands

19 files changed, 2932 insertions, 700 deletions

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
new file mode 100644
index 00000000000..6267fd204c4
--- /dev/null
+++ b/mysql-test/r/information_schema.result
@@ -0,0 +1,402 @@
+grant all privileges on test.* to mysqltest_1@localhost;
+select * from information_schema.SCHEMATA where schema_name > 'm';
+CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME
+NULL	mysql	latin1_swedish_ci
+NULL	test	latin1_swedish_ci
+select schema_name from information_schema.schemata;
+schema_name
+mysql
+test
+show databases *;
+CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME
+NULL	mysql	latin1_swedish_ci
+NULL	test	latin1_swedish_ci
+show databases like 't%';
+Database (t%)
+test
+show databases;
+Database
+mysql
+test
+show databases * where schema_name like 't%';
+CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME
+NULL	test	latin1_swedish_ci
+show databases * where schema_name = 't%';
+CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME
+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
+columns_priv
+db
+func
+help_category
+help_keyword
+help_relation
+help_topic
+host
+proc
+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_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_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 * where TABLE_SCHEMA Like "test%";
+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	test	t3	1	test	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE	
+NULL	testtets	t1	1	testtets	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
+show keys where INDEX_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 tables * from test where table_name like 't%';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
+NULL	test	t2	
+NULL	test	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_bin			N		select,insert,update,references	
+show full columns from v1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+c	char(64)	latin1_swedish_ci					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	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	TYPE	COLLATION_NAME	IS_NULLABLE	KEY	COLUMN_DEFAULT	EXTRA	PRIVILIGES	COMMENT
+NULL	testtets	t1	a	1	int	0	11	4	0	NULL	int(11)	NULL	YES		NULL		select,insert,update,references	
+show columns * where table_name = "t1";
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	TYPE	COLLATION_NAME	IS_NULLABLE	KEY	COLUMN_DEFAULT	EXTRA	PRIVILIGES	COMMENT
+NULL	testtets	t1	a	1	int	0	11	4	0	NULL	int(11)	NULL	YES		NULL			
+NULL	testtets	t1	b	2	varchar	30	30	30	31	latin1	varchar(30)	latin1_swedish_ci	YES	MUL	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	Description	DEFAULT_COLLATE_NAME	Maxlen
+latin1	ISO 8859-1 West European	latin1_swedish_ci	1
+SHOW CHARACTER SET LIKE 'latin1%';
+Charset	Description	Default collation	Maxlen
+latin1	ISO 8859-1 West European	latin1_swedish_ci	1
+SHOW CHARACTER SET * LIKE 'latin1%';
+CHARACTER_SET_NAME	Description	DEFAULT_COLLATE_NAME	Maxlen
+latin1	ISO 8859-1 West European	latin1_swedish_ci	1
+SHOW CHARACTER SET WHERE CHARACTER_SET_NAME like 'latin1%';
+Charset	Description	Default collation	Maxlen
+latin1	ISO 8859-1 West European	latin1_swedish_ci	1
+SHOW CHARACTER SET CHARACTER_SET_NAME WHERE CHARACTER_SET_NAME like 'latin1%';
+CHARACTER_SET_NAME
+latin1
+SHOW CHARACTER SET *  WHERE CHARACTER_SET_NAME like 'latin1%';
+CHARACTER_SET_NAME	Description	DEFAULT_COLLATE_NAME	Maxlen
+latin1	ISO 8859-1 West European	latin1_swedish_ci	1
+select * from information_schema.COLLATIONS
+where COLLATION_NAME like 'latin1%';
+COLLATION_NAME	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 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 * LIKE 'latin1%';
+COLLATION_NAME	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_NAME 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 COLLATION_NAME WHERE COLLATION_NAME like 'latin1%';
+COLLATION_NAME
+latin1_german1_ci
+latin1_swedish_ci
+latin1_danish_ci
+latin1_german2_ci
+latin1_bin
+latin1_general_ci
+latin1_general_cs
+latin1_spanish_ci
+SHOW COLLATION *  WHERE COLLATION_NAME like 'latin1%';
+COLLATION_NAME	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|
+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 = b.name;
+ROUTINE_NAME	name
+sub1	sub1
+sel2	sel2
+select count(*) from information_schema.ROUTINES;
+count(*)
+2
+create view v0 (c) as select schema_name from information_schema.SCHEMATA;
+select * from v0;
+c
+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 `SCHEMATA`.`SCHEMA_NAME` AS `c` from `information_schema`.`SCHEMATA`	NONE	NO
+NULL	test	v1	select `TABLES`.`TABLE_NAME` AS `c` from `information_schema`.`TABLES` where (`TABLES`.`TABLE_NAME` = _latin1'v1')	NONE	NO
+NULL	test	v2	select `COLUMNS`.`COLUMN_NAME` AS `c` from `information_schema`.`COLUMNS` where (`COLUMNS`.`TABLE_NAME` = _latin1'v2')	NONE	NO
+NULL	test	v3	select `CHARACTER_SETS`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`CHARACTER_SETS` where (`CHARACTER_SETS`.`CHARACTER_SET_NAME` like _latin1'latin1%')	NONE	NO
+NULL	test	v4	select `COLLATIONS`.`COLLATION_NAME` AS `c` from `information_schema`.`COLLATIONS` where (`COLLATIONS`.`COLLATION_NAME` like _latin1'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	CREATE VIEW	YES
+'mysqltest_1'@'localhost'	NULL	test	SHOW VIEW	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';
+delete from mysql.db where user='mysqltest_1';
+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	CONSTRAINT_METHOD
+NULL	test	PRIMARY	test	t1	PRIMARY	NULL
+NULL	test	constraint_1	test	t1	UNIQUE	NULL
+NULL	test	key_1	test	t1	UNIQUE	NULL
+NULL	test	key_2	test	t1	UNIQUE	NULL
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_SCHEMA= "test";
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+NULL	test	PRIMARY	test	t1	a	1	NULL	NULL	NULL
+NULL	test	constraint_1	test	t1	a	1	NULL	NULL	NULL
+NULL	test	key_1	test	t1	a	1	NULL	NULL	NULL
+NULL	test	key_2	test	t1	a	1	NULL	NULL	NULL
+drop table t1;
+CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
+FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE,
+FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB;
+select * from information_schema.TABLE_CONSTRAINTS where
+TABLE_SCHEMA= "test";
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_METHOD
+NULL	test	PRIMARY	test	t1	PRIMARY	NULL
+NULL	test	PRIMARY	test	t2	PRIMARY	NULL
+NULL	test	t2_ibfk_1	test	t2	FOREIGN	ON DELETE CASCADE
+NULL	test	t2_ibfk_2	test	t2	FOREIGN	ON UPDATE CASCADE
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_SCHEMA= "test";
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+NULL	test	PRIMARY	test	t1	id	1	NULL	NULL	NULL
+NULL	test	PRIMARY	test	t2	id	1	NULL	NULL	NULL
+NULL	test	t2_ibfk_1	test	t2	t1_id	1	test	t1	id
+NULL	test	t2_ibfk_2	test	t2	t1_id	1	test	t1	id
+select table_name from information_schema.TABLES where table_schema like "test%";
+table_name
+t1
+t2
+select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
+table_name	column_name
+t1	id
+t2	id
+t2	t1_id
+select ROUTINE_NAME from information_schema.ROUTINES;
+ROUTINE_NAME
+sel2
+sub1
+delete from mysql.user where user='mysqltest_1';
+drop table t2;
+drop table t1;
+drop procedure sel2;
+drop function sub1;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
new file mode 100644
index 00000000000..017bf465d53
--- /dev/null
+++ b/mysql-test/t/information_schema.test
@@ -0,0 +1,188 @@
+
+# Test for information_schema.schemata &
+# show databases
+
+grant all privileges on test.* to mysqltest_1@localhost;
+
+select * from information_schema.SCHEMATA where schema_name > 'm';
+select schema_name from information_schema.schemata;
+show databases *;
+show databases like 't%';
+show databases;
+show databases * where schema_name like 't%';
+show databases * where schema_name = 't%';
+
+# Test for information_schema.tables &
+# show tables
+
+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;
+select c,table_name from v1 
+left join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
+select c, v2.table_name from v1
+right join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
+select table_name from information_schema.TABLES
+where table_schema = "testtets" and table_name like "t%";
+
+select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets";
+show keys * where TABLE_SCHEMA Like "test%";
+show keys where INDEX_NAME = "a_data";
+
+show tables like 't%';
+--replace_column 15 # 16 #
+show tables * from test where table_name like 't%';
+--replace_column 12 # 13 #
+show table status;
+show full columns from t3 like "a%";
+show full columns from mysql.db like "Insert%";
+show full columns from v1;
+select * from information_schema.COLUMNS where table_name="t1"
+and column_name= "a";
+show columns * where table_name = "t1";
+
+drop view v1;
+drop tables testtets.t4, testtets.t1, t2, t3;
+drop database testtets;
+
+# Test for information_schema.CHARACTER_SETS &
+# SHOW CHARACTER SET
+
+select * from information_schema.CHARACTER_SETS
+where CHARACTER_SET_NAME like 'latin1%';
+SHOW CHARACTER SET LIKE 'latin1%';
+SHOW CHARACTER SET * LIKE 'latin1%';
+SHOW CHARACTER SET WHERE CHARACTER_SET_NAME like 'latin1%';
+SHOW CHARACTER SET CHARACTER_SET_NAME WHERE CHARACTER_SET_NAME like 'latin1%';
+SHOW CHARACTER SET *  WHERE CHARACTER_SET_NAME like 'latin1%';
+
+# Test for information_schema.COLLATIONS &
+# SHOW COLLATION
+
+select * from information_schema.COLLATIONS
+where COLLATION_NAME like 'latin1%';
+SHOW COLLATION LIKE 'latin1%';
+SHOW COLLATION * LIKE 'latin1%';
+SHOW COLLATION WHERE COLLATION_NAME like 'latin1%';
+SHOW COLLATION COLLATION_NAME WHERE COLLATION_NAME like 'latin1%';
+SHOW COLLATION *  WHERE COLLATION_NAME like 'latin1%';
+
+select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
+where COLLATION_NAME like 'latin1%';
+
+# Test for information_schema.ROUTINES &
+#
+
+create function sub1(i int) returns int
+  return i+1;
+delimiter |;
+create procedure sel2()
+begin
+  select * from t1;
+  select * from t2;
+end|
+delimiter ;|
+
+--replace_column 5 # 6 #
+show procedure status;
+--replace_column 5 # 6 #
+show function status;
+select a.ROUTINE_NAME from information_schema.ROUTINES a,
+information_schema.SCHEMATA b where
+a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
+--replace_column 3 #
+explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
+information_schema.SCHEMATA b where
+a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
+
+select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
+mysql.proc b where a.ROUTINE_NAME = b.name;
+select count(*) from information_schema.ROUTINES;
+
+#
+# Test for views
+#
+create view v0 (c) as select schema_name from information_schema.SCHEMATA;
+select * from v0;
+--replace_column 3 #
+explain select * from v0;
+create view v1 (c) as select table_name from information_schema.TABLES
+where table_name="v1";
+select * from v1;
+create view v2 (c) as select column_name from information_schema.COLUMNS
+where table_name="v2";
+select * from v2;
+create view v3 (c) as select CHARACTER_SET_NAME from information_schema.CHARACTER_SETS
+where CHARACTER_SET_NAME like "latin1%";
+select * from v3;
+create view v4 (c) as select COLLATION_NAME from information_schema.COLLATIONS
+where COLLATION_NAME like "latin1%";
+select * from v4;
+show keys from v4;
+select * from information_schema.VIEWS where TABLE_NAME like "v%";
+drop view v0, v1, v2, v3, v4;
+
+#
+# Test for privileges tables
+#
+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%';
+select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
+select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
+select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
+delete from mysql.user where user='mysqltest_1';
+delete from mysql.db where user='mysqltest_1';
+delete from mysql.tables_priv where user='mysqltest_1';
+delete from mysql.columns_priv where user='mysqltest_1';
+flush privileges;
+drop table t1;
+
+
+#
+# Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables
+#
+
+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;
+select * from information_schema.TABLE_CONSTRAINTS where
+TABLE_SCHEMA= "test";
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_SCHEMA= "test";
+drop table t1;
+
+CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
+FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE,
+FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB;
+select * from information_schema.TABLE_CONSTRAINTS where
+TABLE_SCHEMA= "test";
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_SCHEMA= "test";
+
+connect (user1,localhost,mysqltest_1,,);
+connection user1;
+select table_name from information_schema.TABLES where table_schema like "test%";
+select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
+select ROUTINE_NAME from information_schema.ROUTINES;
+disconnect user1;
+connection default;
+delete from mysql.user where user='mysqltest_1';
+drop table t2;
+drop table t1;
+drop procedure sel2;
+drop function sub1;
+ 
\ No newline at end of file
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index 370458c6e01..7f92f9fd899 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -4674,6 +4674,104 @@ ha_innobase::get_foreign_key_create_info(void)
   	return(str);
 }
 
+
+int 
+ha_innobase::get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list)
+{
+  dict_foreign_t* foreign;
+
+  DBUG_ENTER("get_foreign_key_list");
+  row_prebuilt_t* prebuilt = (row_prebuilt_t*)innobase_prebuilt;
+  ut_a(prebuilt != NULL);
+  update_thd(current_thd);
+  prebuilt->trx->op_info = (char*)"getting list of foreign keys";
+  trx_search_latch_release_if_reserved(prebuilt->trx);
+  mutex_enter(&(dict_sys->mutex));
+  foreign = UT_LIST_GET_FIRST(prebuilt->table->foreign_list);
+
+  while (foreign != NULL) 
+  {
+    uint i;
+    FOREIGN_KEY_INFO f_key_info;
+    LEX_STRING *name= 0;
+    const char *tmp_buff;
+
+    tmp_buff= foreign->id;
+    i= 0;
+    while (tmp_buff[i] != '/')
+      i++;
+    tmp_buff+= i + 1;
+    f_key_info.forein_id= make_lex_string(thd, f_key_info.forein_id,
+                                          tmp_buff, strlen(tmp_buff), 1);
+    tmp_buff= foreign->referenced_table_name;
+    i= 0;
+    while (tmp_buff[i] != '/')
+      i++;
+    f_key_info.referenced_db= make_lex_string(thd, f_key_info.referenced_db,
+                                              tmp_buff, i, 1);
+    tmp_buff+= i + 1;
+    f_key_info.referenced_table= make_lex_string(thd, 
+                                                 f_key_info.referenced_table,
+                                                 tmp_buff, strlen(tmp_buff), 1);
+
+    for (i= 0;;)
+    {
+      tmp_buff= foreign->foreign_col_names[i];
+      name= make_lex_string(thd, name, tmp_buff, strlen(tmp_buff), 1);
+      f_key_info.foreign_fields.push_back(name);
+      tmp_buff= foreign->referenced_col_names[i];
+      name= make_lex_string(thd, name, tmp_buff, strlen(tmp_buff), 1);
+      f_key_info.referenced_fields.push_back(name);
+      if (++i >= foreign->n_fields)
+        break;
+    }
+
+    ulong length= 0;
+    if (foreign->type == DICT_FOREIGN_ON_DELETE_CASCADE)
+    {
+      length=17;
+      tmp_buff= "ON DELETE CASCADE";
+    }	
+    else if (foreign->type == DICT_FOREIGN_ON_DELETE_SET_NULL)
+    {
+      length=18;
+      tmp_buff= "ON DELETE SET NULL";
+    }
+    else if (foreign->type == DICT_FOREIGN_ON_DELETE_NO_ACTION)
+    {
+      length=19;
+      tmp_buff= "ON DELETE NO ACTION";
+    }
+    else if (foreign->type == DICT_FOREIGN_ON_UPDATE_CASCADE)
+    {
+      length=17;
+      tmp_buff= "ON UPDATE CASCADE";
+    }
+    else if (foreign->type == DICT_FOREIGN_ON_UPDATE_SET_NULL)
+    {
+      length=18;
+      tmp_buff= "ON UPDATE SET NULL";
+    }
+    else if (foreign->type == DICT_FOREIGN_ON_UPDATE_NO_ACTION)
+    {
+      length=19;
+      tmp_buff= "ON UPDATE NO ACTION";
+    }
+    f_key_info.constraint_method= make_lex_string(thd,
+                                                  f_key_info.constraint_method,
+                                                  tmp_buff, length, 1);
+
+    FOREIGN_KEY_INFO *pf_key_info= ((FOREIGN_KEY_INFO *) 
+                                    thd->memdup((gptr) &f_key_info,
+                                                sizeof(FOREIGN_KEY_INFO)));
+    f_key_list->push_back(pf_key_info);
+    foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
+  }
+  mutex_exit(&(dict_sys->mutex));
+  prebuilt->trx->op_info = (char*)"";
+  DBUG_RETURN(0);
+}
+
 /***********************************************************************
 Checks if a table is referenced by a foreign key. The MySQL manual states that
 a REPLACE is either equivalent to an INSERT, or DELETE(s) + INSERT. Only a
diff --git a/sql/ha_innodb.h b/sql/ha_innodb.h
index 7e337afed0e..f9588bee055 100644
--- a/sql/ha_innodb.h
+++ b/sql/ha_innodb.h
@@ -162,6 +162,7 @@ class ha_innobase: public handler
 	int check(THD* thd, HA_CHECK_OPT* check_opt);
         char* update_table_comment(const char* comment);
 	char* get_foreign_key_create_info();
+        int get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list);
   	uint referenced_by_foreign_key();
 	void free_foreign_key_create_info(char* str);	
   	THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
diff --git a/sql/handler.h b/sql/handler.h
index 858a7861dba..31c6e2f902b 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -223,6 +223,8 @@ typedef struct st_ha_create_information
 
 struct st_table;
 typedef struct st_table TABLE;
+struct st_foreign_key_info;
+typedef struct st_foreign_key_info FOREIGN_KEY_INFO;
 
 typedef struct st_ha_check_opt
 {
@@ -465,6 +467,8 @@ public:
   virtual char* get_foreign_key_create_info()
   { return(NULL);}  /* gets foreign key create string from InnoDB */
   /* used in REPLACE; is > 0 if table is referred by a FOREIGN KEY */
+  virtual int get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list)
+  { return 0; }
   virtual uint referenced_by_foreign_key() { return 0;}
   virtual void init_table_handle_for_HANDLER()
   { return; }       /* prepare InnoDB for HANDLER */
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 62e4f849c67..5af64604cbd 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -247,6 +247,7 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
    key checks in some cases */
 #define OPTION_RELAXED_UNIQUE_CHECKS    (1L << 27)
 #define SELECT_NO_UNLOCK                (1L << 28)
+#define OPTION_SCHEMA_TABLE             (1L << 29)
 
 /* The rest of the file is included in the server only */
 #ifndef MYSQL_CLIENT
@@ -405,7 +406,8 @@ typedef Comp_creator* (*chooser_compare_func_creator)(bool invert);
 void free_items(Item *item);
 void cleanup_items(Item *item);
 class THD;
-void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0);
+void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0,
+                         TABLE *stopper= 0);
 bool check_one_table_access(THD *thd, ulong privilege,
 			   TABLE_LIST *tables);
 bool check_some_access(THD *thd, ulong want_access, TABLE_LIST *table);
@@ -692,14 +694,7 @@ void free_des_key_file();
 int mysql_do(THD *thd, List<Item> &values);
 
 /* sql_show.cc */
-int mysqld_show_dbs(THD *thd,const char *wild);
 int mysqld_show_open_tables(THD *thd,const char *wild);
-int mysqld_show_tables(THD *thd, const char *db, const char *wild,
-		       bool verbose);
-int mysqld_extend_show_tables(THD *thd,const char *db,const char *wild);
-int mysqld_show_fields(THD *thd,TABLE_LIST *table, const char *wild,
-		       bool verbose);
-int mysqld_show_keys(THD *thd, TABLE_LIST *table);
 int mysqld_show_logs(THD *thd);
 void append_identifier(THD *thd, String *packet, const char *name,
 		       uint length);
@@ -718,14 +713,32 @@ int mysqld_show(THD *thd, const char *wild, show_var_st *variables,
 		struct system_status_var *status_var);
 int mysql_find_files(THD *thd,List<char> *files, const char *db,
                 const char *path, const char *wild, bool dir);
-int mysqld_show_charsets(THD *thd,const char *wild);
-int mysqld_show_collations(THD *thd,const char *wild);
 int mysqld_show_storage_engines(THD *thd);
 int mysqld_show_privileges(THD *thd);
 int mysqld_show_column_types(THD *thd);
 int mysqld_help (THD *thd, const char *text);
 void calc_sum_of_all_status(STATUS_VAR *to);
 
+
+
+/* information schema */
+extern LEX_STRING information_schema_name;
+LEX_STRING *make_lex_string(THD *thd, LEX_STRING *lex_str,
+                            const char* str, uint length,
+                            bool allocate_lex_string= 0);
+ST_SCHEMA_TABLE *find_schema_table(THD *thd, const char* table_name);
+ST_SCHEMA_TABLE *get_schema_table(enum enum_schema_tables schema_table_idx);
+int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident,
+                         enum enum_schema_tables schema_table_idx);
+int make_schema_select(THD *thd,  SELECT_LEX *sel,
+                       enum enum_schema_tables schema_table_idx);
+int mysql_schema_table(THD *thd, LEX *lex, TABLE_LIST *table_list);
+int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
+int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
+int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
+int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
+int get_schema_tables_result(JOIN *join);
+
 /* sql_prepare.cc */
 int mysql_stmt_prepare(THD *thd, char *packet, uint packet_length, 
                        LEX_STRING *name=NULL);
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 30033bc39eb..f9a51a8348f 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -131,7 +131,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds)
           for (table= tables; table; table= table->next_local)
           {
             if (outer_tables || (table->table->file->table_flags() &
-                                 HA_NOT_EXACT_COUNT))
+                                 HA_NOT_EXACT_COUNT) || table->schema_table)
             {
               const_result= 0;			// Can't optimize left join
               break;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 59456c0eb2c..e796f275509 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -2851,7 +2851,8 @@ bool check_grant(THD *thd, ulong want_access, TABLE_LIST *tables,
   for (table= tables; table && number--; table= table->next_global)
   {
     GRANT_TABLE *grant_table;
-    if (!(~table->grant.privilege & want_access) || table->derived)
+    if (!(~table->grant.privilege & want_access) || 
+        table->derived || table->schema_table)
     {
       /*
         It is subquery in the FROM clause. VIEW set table->derived after
@@ -3928,6 +3929,221 @@ int wild_case_compare(CHARSET_INFO *cs, const char *str,const char *wildstr)
   DBUG_RETURN (*str != '\0');
 }
 
+
+void update_schema_privilege(TABLE *table, char *buff, const char* db,
+                             const char* t_name, const char* column,
+                             uint col_length, const char *priv, 
+                             uint priv_length, const char* is_grantable)
+{
+  int i= 2;
+  CHARSET_INFO *cs= system_charset_info;
+  restore_record(table, default_values);
+  table->field[0]->store(buff, strlen(buff), cs);
+  if (db)
+    table->field[i++]->store(db, strlen(db), cs);
+  if (t_name)
+    table->field[i++]->store(t_name, strlen(t_name), cs);
+  if (column)
+    table->field[i++]->store(column, col_length, cs);
+  table->field[i++]->store(priv, priv_length, cs);
+  table->field[i]->store(is_grantable, strlen(is_grantable), cs);
+  table->file->write_row(table->record[0]);
+}
+
+
+int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  uint counter;
+  ACL_USER *acl_user;
+  ulong want_access;
+
+  char buff[100];
+  TABLE *table= tables->table;
+  DBUG_ENTER("fill_schema_user_privileges");
+  for (counter=0 ; counter < acl_users.elements ; counter++)
+  {
+    const char *user,*host, *is_grantable="YES";
+    acl_user=dynamic_element(&acl_users,counter,ACL_USER*);
+    if (!(user=acl_user->user))
+      user= "";
+    if (!(host=acl_user->host.hostname))
+      host= "";
+    want_access= acl_user->access;
+    if (!(want_access & GRANT_ACL))
+      is_grantable= "NO";
+
+    strxmov(buff,"'",user,"'@'",host,"'",NullS);
+    if (!(want_access & ~GRANT_ACL))
+      update_schema_privilege(table, buff, 0, 0, 0, 0, "USAGE", 5, is_grantable);
+    else
+    {
+      uint priv_id;
+      ulong j,test_access= want_access & ~GRANT_ACL;
+      for (priv_id=0, j = SELECT_ACL;j <= GLOBAL_ACLS; priv_id++,j <<= 1)
+      {
+	if (test_access & j)
+          update_schema_privilege(table, buff, 0, 0, 0, 0, 
+                                  command_array[priv_id],
+                                  command_lengths[priv_id], is_grantable);
+      }
+    }
+  }
+#endif
+  DBUG_RETURN(0);
+}
+
+
+int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  uint counter;
+  ACL_DB *acl_db;
+  ulong want_access;
+  char buff[100];
+  TABLE *table= tables->table;
+  DBUG_ENTER("fill_schema_schema_privileges");
+
+  for (counter=0 ; counter < acl_dbs.elements ; counter++)
+  {
+    const char *user, *host, *is_grantable="YES";
+
+    acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*);
+    if (!(user=acl_db->user))
+      user= "";
+    if (!(host=acl_db->host.hostname))
+      host= "";
+
+    want_access=acl_db->access;
+    if (want_access)
+    {
+      if (!(want_access & GRANT_ACL))
+      {
+        is_grantable= "NO";
+      }
+      strxmov(buff,"'",user,"'@'",host,"'",NullS);
+      if (!(want_access & ~GRANT_ACL))
+        update_schema_privilege(table, buff, acl_db->db, 0, 0,
+                                0, "USAGE", 5, is_grantable);
+      else
+      {
+        int cnt;
+        ulong j,test_access= want_access & ~GRANT_ACL;
+        for (cnt=0, j = SELECT_ACL; j <= DB_ACLS; cnt++,j <<= 1)
+          if (test_access & j)
+            update_schema_privilege(table, buff, acl_db->db, 0, 0, 0,
+                                    command_array[cnt], command_lengths[cnt],
+                                    is_grantable);
+      }
+    }
+  }
+#endif
+  DBUG_RETURN(0);
+}
+
+
+int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  uint index;
+  char buff[100];
+  TABLE *table= tables->table;
+  DBUG_ENTER("fill_schema_table_privileges");
+
+  for (index=0 ; index < column_priv_hash.records ; index++)
+  {
+    const char *user, *is_grantable= "YES";
+    GRANT_TABLE *grant_table= (GRANT_TABLE*) hash_element(&column_priv_hash,
+							  index);
+    if (!(user=grant_table->user))
+      user= "";
+    ulong table_access= grant_table->privs;
+    if (table_access != 0)
+    {
+      ulong test_access= table_access & ~GRANT_ACL;
+      if (!(table_access & GRANT_ACL))
+        is_grantable= "NO";
+
+      strxmov(buff,"'",user,"'@'",grant_table->orig_host,"'",NullS);
+      if (!test_access)
+        update_schema_privilege(table, buff, grant_table->db, grant_table->tname,
+                                0, 0, "USAGE", 5, is_grantable);
+      else
+      {
+        ulong j;
+        int cnt;
+        for (cnt= 0, j= SELECT_ACL; j <= TABLE_ACLS; cnt++, j<<= 1)
+        {
+          if (test_access & j)
+            update_schema_privilege(table, buff, grant_table->db, 
+                                    grant_table->tname, 0, 0, command_array[cnt],
+                                    command_lengths[cnt], is_grantable);
+        }
+      }
+    }
+  }
+#endif
+  DBUG_RETURN(0);
+}
+
+
+int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  uint index;
+  char buff[100];
+  TABLE *table= tables->table;
+  DBUG_ENTER("fill_schema_table_privileges");
+
+  for (index=0 ; index < column_priv_hash.records ; index++)
+  {
+    const char *user, *is_grantable= "YES";
+    GRANT_TABLE *grant_table= (GRANT_TABLE*) hash_element(&column_priv_hash,
+							  index);
+    if (!(user=grant_table->user))
+      user= "";
+    ulong table_access= grant_table->cols;
+    if (table_access != 0)
+    {
+      ulong test_access= grant_table->cols & ~GRANT_ACL;
+      if (!(table_access & GRANT_ACL))
+        is_grantable= "NO";
+
+      strxmov(buff,"'",user,"'@'",grant_table->orig_host,"'",NullS);
+      if (!test_access)
+        continue;
+      else
+      {
+        ulong j;
+        int cnt;
+        for (cnt= 0, j= SELECT_ACL; j <= TABLE_ACLS; cnt++, j<<= 1)
+        {
+          if (test_access & j)
+          {
+            for (uint col_index=0 ;
+                 col_index < grant_table->hash_columns.records ;
+                 col_index++)
+            {
+              GRANT_COLUMN *grant_column = (GRANT_COLUMN*)
+                hash_element(&grant_table->hash_columns,col_index);
+              if ((grant_column->rights & j) && (table_access & j))
+                  update_schema_privilege(table, buff, grant_table->db,
+                                          grant_table->tname,
+                                          grant_column->column,
+                                          grant_column->key_length,
+                                          command_array[cnt],
+                                          command_lengths[cnt], is_grantable);
+            }
+          }
+        }
+      }
+    }
+  }
+#endif
+  DBUG_RETURN(0);
+}
+
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 /*
   fill effective privileges for table
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index e690673c934..9dc66f46afa 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -370,7 +370,8 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
     Put all normal tables used by thread in free list.
 */
 
-void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
+void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, 
+                         TABLE *stopper)
 {
   bool found_old_table;
   DBUG_ENTER("close_thread_tables");
@@ -408,7 +409,7 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived)
   DBUG_PRINT("info", ("thd->open_tables: %p", thd->open_tables));
 
  found_old_table= 0;
-  while (thd->open_tables)
+  while (thd->open_tables != stopper)
     found_old_table|=close_thread_table(thd, &thd->open_tables);
   thd->some_tables_deleted=0;
 
@@ -1656,6 +1657,12 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
      */
     if (tables->derived)
       continue;
+    if (tables->schema_table)
+    {
+      if (!mysql_schema_table(thd, thd->lex, tables))
+        continue;
+      DBUG_RETURN(-1);
+    }
     (*counter)++;
     if (!tables->table &&
 	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh)))
@@ -2904,7 +2911,7 @@ insert_fields(THD *thd, TABLE_LIST *tables, const char *db_name,
                                       &view_iter))
             goto err;
         }
-        else
+        else if (!tables->schema_table)
         {
           table_iter.set(tables);
           if (check_grant_all_columns(thd, SELECT_ACL, &table->grant,
@@ -2984,7 +2991,8 @@ insert_fields(THD *thd, TABLE_LIST *tables, const char *db_name,
               db= tables->db;
               tab= tables->real_name;
             }
-            if (!(fld->have_privileges= (get_column_grant(thd,
+            if (!tables->schema_table && 
+                !(fld->have_privileges= (get_column_grant(thd,
                                                           &table->grant,
                                                           db,
                                                           tab,
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 86219abc632..68203d8ba20 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1057,6 +1057,7 @@ void st_select_lex::init_query()
   first_cond_optimization= 1;
   parsing_place= NO_MATTER;
   no_wrap_view_item= 0;
+  link_next= 0;
 }
 
 void st_select_lex::init_select()
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index fd1c1dee0b7..18c13efc528 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -56,7 +56,7 @@ enum enum_sql_command {
   SQLCOM_SHOW_INNODB_STATUS,
   SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT,
   SQLCOM_SHOW_GRANTS, SQLCOM_SHOW_CREATE, SQLCOM_SHOW_CHARSETS,
-  SQLCOM_SHOW_COLLATIONS, SQLCOM_SHOW_CREATE_DB,
+  SQLCOM_SHOW_COLLATIONS, SQLCOM_SHOW_CREATE_DB, SQLCOM_SHOW_TABLE_STATUS,
 
   SQLCOM_LOAD,SQLCOM_SET_OPTION,SQLCOM_LOCK_TABLES,SQLCOM_UNLOCK_TABLES,
   SQLCOM_GRANT,
@@ -692,7 +692,7 @@ typedef struct st_lex
   LEX_MASTER_INFO mi;				// used by CHANGE MASTER
   USER_RESOURCES mqh;
   ulong thread_id,type;
-  enum_sql_command sql_command;
+  enum_sql_command sql_command, orig_sql_command;
   thr_lock_type lock_option, multi_lock_option;
   enum SSL_type ssl_type;			/* defined in violite.h */
   enum my_lex_states next_state;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 726c4faa7bc..91f8defb2a7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1868,6 +1868,109 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 }
 
 
+int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident,
+                         enum enum_schema_tables schema_table_idx)
+{
+  DBUG_ENTER("prepare_schema_table");
+  SELECT_LEX *sel= 0;
+  switch(schema_table_idx) {
+  case SCH_SCHEMATA:
+#if defined(DONT_ALLOW_SHOW_COMMANDS)
+    send_error(thd,ER_NOT_ALLOWED_COMMAND);   /* purecov: inspected */
+    DBUG_RETURN(1);
+#else
+    if ((specialflag & SPECIAL_SKIP_SHOW_DB) &&
+	check_global_access(thd, SHOW_DB_ACL))
+      DBUG_RETURN(1);
+    break;
+#endif
+  case SCH_TABLE_NAMES:
+  case SCH_TABLES:
+  case SCH_VIEWS:
+#ifdef DONT_ALLOW_SHOW_COMMANDS
+    send_error(thd,ER_NOT_ALLOWED_COMMAND);	/* purecov: inspected */
+    DBUG_RETURN(1);
+#else
+    {
+      char *db= lex->select_lex.db ? lex->select_lex.db : thd->db;
+      if (!db)
+      {
+	send_error(thd,ER_NO_DB_ERROR);		/* purecov: inspected */
+        DBUG_RETURN(1);				/* purecov: inspected */
+      }
+      remove_escape(db);				// Fix escaped '_'
+      if (check_db_name(db))
+      {
+        net_printf(thd,ER_WRONG_DB_NAME, db);
+        DBUG_RETURN(1);
+      }
+      if (check_access(thd,SELECT_ACL,db,&thd->col_access,0,0))
+        DBUG_RETURN(1);			        /* purecov: inspected */
+      if (!thd->col_access && check_grant_db(thd,db))
+      {
+	net_printf(thd, ER_DBACCESS_DENIED_ERROR,
+		   thd->priv_user,
+		   thd->priv_host,
+		   db);
+	DBUG_RETURN(1);
+      }
+      lex->select_lex.db= db;
+      break;
+    }
+#endif
+  case SCH_COLUMNS:
+  case SCH_STATISTICS:
+#ifdef DONT_ALLOW_SHOW_COMMANDS
+    send_error(thd,ER_NOT_ALLOWED_COMMAND);	/* purecov: inspected */
+    DBUG_RETURN(1);
+#else
+    if (table_ident)
+    {
+      TABLE_LIST **query_tables_last= lex->query_tables_last;
+      sel= new SELECT_LEX();
+      sel->init_query();
+      if(!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ, 
+                                 (List<String> *) 0, (List<String> *) 0))
+        DBUG_RETURN(1);
+      lex->query_tables_last= query_tables_last;
+      TABLE_LIST *table_list= (TABLE_LIST*) sel->table_list.first;
+      char *db= table_list->db;
+      remove_escape(db);			// Fix escaped '_'
+      remove_escape(table_list->real_name);
+      if (check_access(thd,SELECT_ACL | EXTRA_ACL,db,
+                       &table_list->grant.privilege, 0, 0))
+        DBUG_RETURN(1);				/* purecov: inspected */
+      if (grant_option && check_grant(thd, SELECT_ACL, table_list, 2,
+                                      UINT_MAX, 0))
+        DBUG_RETURN(1);
+      break;
+    }
+#endif
+  case SCH_PROCEDURES:
+  case SCH_CHARSETS:
+  case SCH_COLLATIONS:
+  case SCH_COLLATION_CHARACTER_SET_APPLICABILITY:
+  case SCH_USER_PRIVILEGES:
+  case SCH_SCHEMA_PRIVILEGES:
+  case SCH_TABLE_PRIVILEGES:
+  case SCH_COLUMN_PRIVILEGES:
+  case SCH_TABLE_CONSTRAINTS:
+  case SCH_KEY_COLUMN_USAGE:
+  default:
+    break;
+  }
+  
+  SELECT_LEX *select_lex= lex->current_select;
+  if (make_schema_select(thd, select_lex, schema_table_idx))
+  {
+    DBUG_RETURN(1);
+  }
+  TABLE_LIST *table_list= (TABLE_LIST*) select_lex->table_list.first;
+  table_list->schema_select_lex= sel;
+  DBUG_RETURN(0);
+}
+
+
 /*
   Read query from packet and store in thd->query
   Used in COM_QUERY and COM_PREPARE
@@ -3004,17 +3107,6 @@ unsent_create_error:
     else
       res = mysql_drop_index(thd, first_table, &lex->alter_info);
     break;
-  case SQLCOM_SHOW_DATABASES:
-#if defined(DONT_ALLOW_SHOW_COMMANDS)
-    send_error(thd,ER_NOT_ALLOWED_COMMAND);   /* purecov: inspected */
-    goto error;
-#else
-    if ((specialflag & SPECIAL_SKIP_SHOW_DB) &&
-	check_global_access(thd, SHOW_DB_ACL))
-      goto error;
-    res= mysqld_show_dbs(thd, (lex->wild ? lex->wild->ptr() : NullS));
-    break;
-#endif
   case SQLCOM_SHOW_PROCESSLIST:
     if (!thd->priv_user[0] && check_global_access(thd,PROCESS_ACL))
       break;
@@ -3062,95 +3154,9 @@ unsent_create_error:
       break;
     }
 #endif
-  case SQLCOM_SHOW_TABLES:
-    /* FALL THROUGH */
-#ifdef DONT_ALLOW_SHOW_COMMANDS
-    send_error(thd,ER_NOT_ALLOWED_COMMAND);	/* purecov: inspected */
-    goto error;
-#else
-    {
-      char *db=select_lex->db ? select_lex->db : thd->db;
-      if (!db)
-      {
-	send_error(thd,ER_NO_DB_ERROR);		/* purecov: inspected */
-	goto error;				/* purecov: inspected */
-      }
-      remove_escape(db);				// Fix escaped '_'
-      if (check_db_name(db))
-      {
-        net_printf(thd,ER_WRONG_DB_NAME, db);
-        goto error;
-      }
-      if (check_access(thd,SELECT_ACL,db,&thd->col_access,0,0))
-	goto error;				/* purecov: inspected */
-      if (!thd->col_access && check_grant_db(thd,db))
-      {
-	net_printf(thd, ER_DBACCESS_DENIED_ERROR,
-		   thd->priv_user,
-		   thd->priv_host,
-		   db);
-	goto error;
-      }
-      /* grant is checked in mysqld_show_tables */
-      if (lex->describe)
-        res= mysqld_extend_show_tables(thd,db,
-				       (lex->wild ? lex->wild->ptr() : NullS));
-      else
-	res= mysqld_show_tables(thd, db,
-				(lex->wild ? lex->wild->ptr() : NullS),
-				lex->verbose);
-      break;
-    }
-#endif
   case SQLCOM_SHOW_OPEN_TABLES:
     res= mysqld_show_open_tables(thd,(lex->wild ? lex->wild->ptr() : NullS));
     break;
-  case SQLCOM_SHOW_CHARSETS:
-    res= mysqld_show_charsets(thd,(lex->wild ? lex->wild->ptr() : NullS));
-    break;
-  case SQLCOM_SHOW_COLLATIONS:
-    res= mysqld_show_collations(thd,(lex->wild ? lex->wild->ptr() : NullS));
-    break;
-  case SQLCOM_SHOW_FIELDS:
-    DBUG_ASSERT(first_table == all_tables && first_table != 0);
-#ifdef DONT_ALLOW_SHOW_COMMANDS
-    send_error(thd,ER_NOT_ALLOWED_COMMAND);	/* purecov: inspected */
-    goto error;
-#else
-    {
-      char *db= first_table->db;
-      remove_escape(db);			// Fix escaped '_'
-      remove_escape(first_table->real_name);
-      if (check_access(thd,SELECT_ACL | EXTRA_ACL,db,
-		       &first_table->grant.privilege, 0, 0))
-	goto error;				/* purecov: inspected */
-      if (grant_option && check_grant(thd, SELECT_ACL, first_table, 2, UINT_MAX, 0))
-	goto error;
-      res= mysqld_show_fields(thd, first_table,
-			      (lex->wild ? lex->wild->ptr() : NullS),
-			      lex->verbose);
-      break;
-    }
-#endif
-  case SQLCOM_SHOW_KEYS:
-    DBUG_ASSERT(first_table == all_tables && first_table != 0);
-#ifdef DONT_ALLOW_SHOW_COMMANDS
-    send_error(thd,ER_NOT_ALLOWED_COMMAND);	/* purecov: inspected */
-    goto error;
-#else
-    {
-      char *db= first_table->db;
-      remove_escape(db);			// Fix escaped '_'
-      remove_escape(first_table->real_name);
-      if (check_access(thd,SELECT_ACL | EXTRA_ACL,db,
-		       &first_table->grant.privilege, 0, 0))
-	goto error;				/* purecov: inspected */
-      if (grant_option && check_grant(thd, SELECT_ACL, all_tables, 2, UINT_MAX, 0))
-	goto error;
-      res= mysqld_show_keys(thd, first_table);
-      break;
-    }
-#endif
   case SQLCOM_CHANGE_DB:
     mysql_change_db(thd,select_lex->db);
     break;
@@ -4234,7 +4240,7 @@ check_table_access(THD *thd, ulong want_access,TABLE_LIST *tables,
   TABLE_LIST *org_tables=tables;
   for (; tables; tables= tables->next_global)
   {
-    if (tables->derived ||
+    if (tables->derived || tables->schema_table ||
         (tables->table && (int)tables->table->tmp_table) ||
         my_tz_check_n_skip_implicit_tables(&tables,
                                            thd->lex->time_zone_tables_used))
@@ -4490,6 +4496,8 @@ mysql_init_select(LEX *lex)
   SELECT_LEX *select_lex= lex->current_select;
   select_lex->init_select();
   select_lex->select_limit= HA_POS_ERROR;
+  lex->orig_sql_command= SQLCOM_END;
+  lex->wild= 0;
   if (select_lex == &lex->select_lex)
   {
     DBUG_ASSERT(lex->result == 0);
@@ -5286,6 +5294,18 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   ptr->force_index= test(table_options & TL_OPTION_FORCE_INDEX);
   ptr->ignore_leaves= test(table_options & TL_OPTION_IGNORE_LEAVES);
   ptr->derived=	    table->sel;
+  if (!my_strcasecmp(system_charset_info, ptr->db,
+                     information_schema_name.str))
+  {
+    ST_SCHEMA_TABLE *schema_table= find_schema_table(thd, ptr->real_name);
+    if (!schema_table)
+    {
+      net_printf(thd, ER_UNKNOWN_TABLE, ptr->real_name,
+                 information_schema_name.str);
+      DBUG_RETURN(0);
+    }
+    ptr->schema_table= schema_table;
+  }
   ptr->select_lex=  lex->current_select;
   ptr->cacheable_table= 1;
   if (use_index_arg)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d07e274b0b7..3c7f1032f51 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1221,6 +1221,12 @@ JOIN::exec()
   List<Item> *curr_fields_list= &fields_list;
   TABLE *curr_tmp_table= 0;
 
+  if ((curr_join->select_lex->options & OPTION_SCHEMA_TABLE) &&
+      get_schema_tables_result(curr_join))
+  {
+    DBUG_VOID_RETURN;
+  }
+
   /* Create a tmp table if distinct or if the sort is too complicated */
   if (need_tmp)
   {
@@ -2129,6 +2135,8 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
 
     s->dependent= tables->dep_tables;
     s->key_dependent= 0;
+    if (tables->schema_table)
+      table->file->records= 2;
 
     s->on_expr_ref= &tables->on_expr;
     if (*s->on_expr_ref)
@@ -7724,7 +7732,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 	blob_count,group_null_items;
   bool	using_unique_constraint=0;
   bool  not_all_columns= !(select_options & TMP_TABLE_ALL_COLUMNS);
-  char	*tmpname,path[FN_REFLEN];
+  char	*tmpname,path[FN_REFLEN], filename[FN_REFLEN];
   byte	*pos,*group_buff;
   uchar *null_flags;
   Field **reg_field, **from_field, **blob_field;
@@ -7746,14 +7754,15 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
     temp_pool_slot = bitmap_set_next(&temp_pool);
 
   if (temp_pool_slot != MY_BIT_NONE) // we got a slot
-    sprintf(path, "%s%s_%lx_%i", mysql_tmpdir, tmp_file_prefix,
-	    current_pid, temp_pool_slot);
+    sprintf(filename, "%s_%lx_%i", tmp_file_prefix,
+            current_pid, temp_pool_slot);
   else // if we run out of slots or we are not using tempool
-    sprintf(path,"%s%s%lx_%lx_%x",mysql_tmpdir,tmp_file_prefix,current_pid,
+    sprintf(filename,"%s%lx_%lx_%x",tmp_file_prefix,current_pid,
             thd->thread_id, thd->tmp_table++);
 
   if (lower_case_table_names)
     my_casedn_str(files_charset_info, path);
+  sprintf(path, "%s%s", mysql_tmpdir, filename);
 
   if (group)
   {
@@ -12879,8 +12888,16 @@ void st_table_list::print(THD *thd, String *str)
     {
       append_identifier(thd, str, db, db_length);
       str->append('.');
-      append_identifier(thd, str, real_name, real_name_length);
-      cmp_name= real_name;
+      if (schema_table)
+      {
+        append_identifier(thd, str, alias, strlen(alias));
+        cmp_name= alias;
+      }
+      else
+      {
+        append_identifier(thd, str, real_name, real_name_length);
+        cmp_name= real_name;
+      }
     }
     if (my_strcasecmp(table_alias_charset, cmp_name, alias))
     {
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 7043379bf8f..2d27faa0e9c 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -21,6 +21,7 @@
 #include "sql_select.h"                         // For select_describe
 #include "sql_acl.h"
 #include "repl_failsafe.h"
+#include "sp_head.h"
 #include <my_dir.h>
 
 #ifdef HAVE_BERKELEY_DB
@@ -43,55 +44,6 @@ static int
 view_store_create_info(THD *thd, TABLE_LIST *table, String *packet);
 
 
-/*
-  Report list of databases
-  A database is a directory in the mysql_data_home directory
-*/
-
-int
-mysqld_show_dbs(THD *thd,const char *wild)
-{
-  Item_string *field=new Item_string("",0,thd->charset());
-  List<Item> field_list;
-  char *end;
-  List<char> files;
-  char *file_name;
-  Protocol *protocol= thd->protocol;
-  DBUG_ENTER("mysqld_show_dbs");
-
-  field->name=(char*) thd->alloc(20+ (wild ? (uint) strlen(wild)+4: 0));
-  field->max_length=NAME_LEN;
-  end=strmov(field->name,"Database");
-  if (wild && wild[0])
-    strxmov(end," (",wild,")",NullS);
-  field_list.push_back(field);
-
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-  if (mysql_find_files(thd,&files,NullS,mysql_data_home,wild,1))
-    DBUG_RETURN(1);
-  List_iterator_fast<char> it(files);
-
-  while ((file_name=it++))
-  {
-#ifndef NO_EMBEDDED_ACCESS_CHECKS
-    if (thd->master_access & (DB_ACLS | SHOW_DB_ACL) ||
-	acl_get(thd->host, thd->ip, thd->priv_user, file_name,0) ||
-	(grant_option && !check_grant_db(thd, file_name)))
-#endif
-    {
-      protocol->prepare_for_resend();
-      protocol->store(file_name, system_charset_info);
-      if (protocol->write())
-	DBUG_RETURN(-1);
-    }
-  }
-  send_eof(thd);
-  DBUG_RETURN(0);
-}
-
-
 /***************************************************************************
   List all open tables in a database
 ***************************************************************************/
@@ -133,70 +85,6 @@ int mysqld_show_open_tables(THD *thd,const char *wild)
 
 
 /***************************************************************************
-** List all tables in a database (fast version)
-** A table is a .frm file in the current databasedir
-***************************************************************************/
-
-int mysqld_show_tables(THD *thd, const char *db, const char *wild,
-		       bool show_type)
-{
-  Item_string *field=new Item_string("",0,thd->charset());
-  List<Item> field_list;
-  char path[FN_LEN],*end;
-  List<char> files;
-  char *file_name;
-  Protocol *protocol= thd->protocol;
-  uint len;
-  DBUG_ENTER("mysqld_show_tables");
-
-  field->name=(char*) thd->alloc(20+(uint) strlen(db)+
-				 (wild ? (uint) strlen(wild)+4:0));
-  end=strxmov(field->name,"Tables_in_",db,NullS);
-  if (wild && wild[0])
-    strxmov(end," (",wild,")",NullS);
-  field->max_length=NAME_LEN;
-  (void) my_snprintf(path, FN_LEN, "%s/%s", mysql_data_home, db);
-  end= path + (len= unpack_dirname(path,path));
-  len= FN_LEN - len;
-  field_list.push_back(field);
-  if (show_type)
-    field_list.push_back(new Item_empty_string("Table_type", 10));
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-  if (mysql_find_files(thd,&files,db,path,wild,0))
-    DBUG_RETURN(-1);
-  List_iterator_fast<char> it(files);
-  while ((file_name=it++))
-  {
-    protocol->prepare_for_resend();
-    protocol->store(file_name, system_charset_info);
-    if (show_type)
-    {
-      my_snprintf(end, len, "/%s%s", file_name, reg_ext);
-      switch (mysql_frm_type(path))
-      {
-      case FRMTYPE_ERROR:
-        protocol->store("ERROR", system_charset_info);
-        break;
-      case FRMTYPE_TABLE:
-        protocol->store("BASE TABLE", system_charset_info);
-        break;
-      case FRMTYPE_VIEW:
-        protocol->store("VIEW", system_charset_info);
-        break;
-      default:
-        DBUG_ASSERT(0); // this should be impossible
-      }
-    }
-    if (protocol->write())
-      DBUG_RETURN(-1);
-  }
-  send_eof(thd);
-  DBUG_RETURN(0);
-}
-
-/***************************************************************************
 ** List all table types supported 
 ***************************************************************************/
 
@@ -483,211 +371,6 @@ mysql_find_files(THD *thd,List<char> *files, const char *db,const char *path,
 
 
 /***************************************************************************
- Extended version of mysqld_show_tables
-***************************************************************************/
-
-int mysqld_extend_show_tables(THD *thd,const char *db,const char *wild)
-{
-  Item *item;
-  List<char> files;
-  List<Item> field_list;
-  char path[FN_LEN];
-  char *file_name;
-  TABLE *table;
-  Protocol *protocol= thd->protocol;
-  TIME time;
-  int res;
-  DBUG_ENTER("mysqld_extend_show_tables");
-
-  (void) sprintf(path,"%s/%s",mysql_data_home,db);
-  (void) unpack_dirname(path,path);
-  field_list.push_back(item=new Item_empty_string("Name",NAME_LEN));
-  field_list.push_back(item=new Item_empty_string("Engine",10));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Version", (longlong) 0, 21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_empty_string("Row_format",10));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Rows",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Avg_row_length",(int32) 0,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Data_length",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Max_data_length",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Index_length",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Data_free",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Auto_increment",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_datetime("Create_time"));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_datetime("Update_time"));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_datetime("Check_time"));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_empty_string("Collation",32));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Checksum",(longlong) 1,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_empty_string("Create_options",255));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_empty_string("Comment",80));
-  item->maybe_null=1;
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-
-  if (mysql_find_files(thd,&files,db,path,wild,0))
-    DBUG_RETURN(-1);
-  List_iterator_fast<char> it(files);
-  while ((file_name=it++))
-  {
-    TABLE_LIST table_list;
-    bzero((char*) &table_list,sizeof(table_list));
-    protocol->prepare_for_resend();
-    protocol->store(file_name, system_charset_info);
-    table_list.db=(char*) db;
-    table_list.real_name= table_list.alias= file_name;
-    table_list.select_lex= &thd->lex->select_lex;
-    if (lower_case_table_names)
-      my_casedn_str(files_charset_info, file_name);
-    if ((res= open_and_lock_tables(thd, &table_list)))
-    {
-      for (uint i=2 ; i < field_list.elements ; i++)
-        protocol->store_null();
-      // Send error to Comment field if possible
-      if (res < 0)
-      {
-        protocol->store(thd->net.last_error, system_charset_info);
-        thd->clear_error();
-      }
-      else
-        DBUG_RETURN(1);
-    }
-    else if (table_list.view)
-    {
-      for (uint i= 2; i < field_list.elements; i++)
-        protocol->store_null();
-      protocol->store("view", system_charset_info);
-    }
-    else
-    {
-      const char *str;
-      handler *file= (table= table_list.table)->file;
-      file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
-      protocol->store(file->table_type(), system_charset_info);
-      protocol->store((ulonglong) table->frm_version);
-      str= ((table->db_options_in_use & HA_OPTION_COMPRESS_RECORD) ?
-	    "Compressed" :
-	    (table->db_options_in_use & HA_OPTION_PACK_RECORD) ?
-	    "Dynamic" : "Fixed");
-      protocol->store(str, system_charset_info);
-      protocol->store((ulonglong) file->records);
-      protocol->store((ulonglong) file->mean_rec_length);
-      protocol->store((ulonglong) file->data_file_length);
-      if (file->max_data_file_length)
-        protocol->store((ulonglong) file->max_data_file_length);
-      else
-        protocol->store_null();
-      protocol->store((ulonglong) file->index_file_length);
-      protocol->store((ulonglong) file->delete_length);
-      if (table->found_next_number_field)
-      {
-        table->next_number_field=table->found_next_number_field;
-        table->next_number_field->reset();
-        file->update_auto_increment();
-        protocol->store(table->next_number_field->val_int());
-        table->next_number_field=0;
-      }
-      else
-        protocol->store_null();
-      if (!file->create_time)
-        protocol->store_null();
-      else
-      {
-        thd->variables.time_zone->gmt_sec_to_TIME(&time, file->create_time);
-        protocol->store(&time);
-      }
-      if (!file->update_time)
-        protocol->store_null();
-      else
-      {
-        thd->variables.time_zone->gmt_sec_to_TIME(&time, file->update_time);
-        protocol->store(&time);
-      }
-      if (!file->check_time)
-        protocol->store_null();
-      else
-      {
-        thd->variables.time_zone->gmt_sec_to_TIME(&time, file->check_time);
-        protocol->store(&time);
-      }
-      str= (table->table_charset ? table->table_charset->name : "default");
-      protocol->store(str, system_charset_info);
-      if (file->table_flags() & HA_HAS_CHECKSUM)
-        protocol->store((ulonglong)file->checksum());
-      else
-        protocol->store_null(); // Checksum
-      {
-        char option_buff[350],*ptr;
-        ptr=option_buff;
-        if (table->min_rows)
-        {
-          ptr=strmov(ptr," min_rows=");
-          ptr=longlong10_to_str(table->min_rows,ptr,10);
-        }
-        if (table->max_rows)
-        {
-          ptr=strmov(ptr," max_rows=");
-          ptr=longlong10_to_str(table->max_rows,ptr,10);
-        }
-        if (table->avg_row_length)
-        {
-          ptr=strmov(ptr," avg_row_length=");
-          ptr=longlong10_to_str(table->avg_row_length,ptr,10);
-        }
-        if (table->db_create_options & HA_OPTION_PACK_KEYS)
-          ptr=strmov(ptr," pack_keys=1");
-        if (table->db_create_options & HA_OPTION_NO_PACK_KEYS)
-          ptr=strmov(ptr," pack_keys=0");
-        if (table->db_create_options & HA_OPTION_CHECKSUM)
-          ptr=strmov(ptr," checksum=1");
-        if (table->db_create_options & HA_OPTION_DELAY_KEY_WRITE)
-          ptr=strmov(ptr," delay_key_write=1");
-        if (table->row_type != ROW_TYPE_DEFAULT)
-          ptr=strxmov(ptr, " row_format=", ha_row_type[(uint) table->row_type],
-                      NullS);
-        if (file->raid_type)
-        {
-          char buff[100];
-          sprintf(buff," raid_type=%s raid_chunks=%d raid_chunksize=%ld",
-                  my_raid_type(file->raid_type), file->raid_chunks, file->raid_chunksize/RAID_BLOCK_SIZE);
-          ptr=strmov(ptr,buff);
-        }
-        protocol->store(option_buff+1,
-			(ptr == option_buff ? 0 : (uint) (ptr-option_buff)-1)
-			, system_charset_info);
-      }
-      {
-	char *comment=table->file->update_table_comment(table->comment);
-	protocol->store(comment, system_charset_info);
-	if (comment != table->comment)
-	  my_free(comment,MYF(0));
-      }
-    }
-    close_thread_tables(thd, 0);
-    if (protocol->write())
-      DBUG_RETURN(-1);
-  }
-  send_eof(thd);
-  DBUG_RETURN(0);
-}
-
-
-/***************************************************************************
 ** List all columns in a table_list->real_name
 ***************************************************************************/
 
@@ -1046,103 +729,6 @@ mysqld_show_logs(THD *thd)
 }
 
 
-int
-mysqld_show_keys(THD *thd, TABLE_LIST *table_list)
-{
-  TABLE *table;
-  Protocol *protocol= thd->protocol;
-  DBUG_ENTER("mysqld_show_keys");
-  DBUG_PRINT("enter",("db: %s  table: %s",table_list->db,
-                      table_list->real_name));
-
-  if (!(table = open_ltable(thd, table_list, TL_UNLOCK)))
-  {
-    send_error(thd);
-    DBUG_RETURN(1);
-  }
-
-  List<Item> field_list;
-  Item *item;
-  field_list.push_back(new Item_empty_string("Table",NAME_LEN));
-  field_list.push_back(new Item_return_int("Non_unique",1, MYSQL_TYPE_TINY));
-  field_list.push_back(new Item_empty_string("Key_name",NAME_LEN));
-  field_list.push_back(new Item_return_int("Seq_in_index",2, MYSQL_TYPE_TINY));
-  field_list.push_back(new Item_empty_string("Column_name",NAME_LEN));
-  field_list.push_back(item=new Item_empty_string("Collation",1));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_int("Cardinality",0,21));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_return_int("Sub_part",3,
-						MYSQL_TYPE_TINY));
-  item->maybe_null=1;
-  field_list.push_back(item=new Item_empty_string("Packed",10));
-  item->maybe_null=1;
-  field_list.push_back(new Item_empty_string("Null",3));
-  field_list.push_back(new Item_empty_string("Index_type",16));
-  field_list.push_back(new Item_empty_string("Comment",255));
-  item->maybe_null=1;
-
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-
-  KEY *key_info=table->key_info;
-  table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK | HA_STATUS_TIME);
-  for (uint i=0 ; i < table->keys ; i++,key_info++)
-  {
-    KEY_PART_INFO *key_part= key_info->key_part;
-    const char *str;
-    for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
-    {
-      protocol->prepare_for_resend();
-      protocol->store(table->table_name, system_charset_info);
-      protocol->store_tiny((longlong) ((key_info->flags & HA_NOSAME) ? 0 :1));
-      protocol->store(key_info->name, system_charset_info);
-      protocol->store_tiny((longlong) (j+1));
-      str=(key_part->field ? key_part->field->field_name :
-	   "?unknown field?");
-      protocol->store(str, system_charset_info);
-      if (table->file->index_flags(i, j, 0) & HA_READ_ORDER)
-        protocol->store(((key_part->key_part_flag & HA_REVERSE_SORT) ?
-			 "D" : "A"), 1, system_charset_info);
-      else
-        protocol->store_null(); /* purecov: inspected */
-      KEY *key=table->key_info+i;
-      if (key->rec_per_key[j])
-      {
-        ha_rows records=(table->file->records / key->rec_per_key[j]);
-        protocol->store((ulonglong) records);
-      }
-      else
-        protocol->store_null();
-
-      /* Check if we have a key part that only uses part of the field */
-      if (!(key_info->flags & HA_FULLTEXT) && (!key_part->field ||
-          key_part->length != table->field[key_part->fieldnr-1]->key_length()))
-        protocol->store_tiny((longlong) key_part->length);
-      else
-        protocol->store_null();
-      protocol->store_null();                   // No pack_information yet
-
-      /* Null flag */
-      uint flags= key_part->field ? key_part->field->flags : 0;
-      char *pos=(char*) ((flags & NOT_NULL_FLAG) ? "" : "YES");
-      protocol->store((const char*) pos, system_charset_info);
-      protocol->store(table->file->index_type(i), system_charset_info);
-      /* Comment */
-      if (!table->keys_in_use.is_set(i))
-	protocol->store("disabled",8, system_charset_info);
-      else
-        protocol->store("", 0, system_charset_info);
-      if (protocol->write())
-        DBUG_RETURN(1); /* purecov: inspected */
-    }
-  }
-  send_eof(thd);
-  DBUG_RETURN(0);
-}
-
-
 /****************************************************************************
   Return only fields for API mysql_list_fields
   Use "show table wildcard" in mysql instead of this
@@ -1816,112 +1402,6 @@ void mysqld_list_processes(THD *thd,const char *user, bool verbose)
   Status functions
 *****************************************************************************/
 
-static bool write_collation(Protocol *protocol, CHARSET_INFO *cs)
-{
-  protocol->prepare_for_resend();
-  protocol->store(cs->name, system_charset_info);
-  protocol->store(cs->csname, system_charset_info);
-  protocol->store_short((longlong) cs->number);
-  protocol->store((cs->state & MY_CS_PRIMARY) ? "Yes" : "",system_charset_info);
-  protocol->store((cs->state & MY_CS_COMPILED)? "Yes" : "",system_charset_info);
-  protocol->store_short((longlong) cs->strxfrm_multiply);
-  return protocol->write();
-}
-
-int mysqld_show_collations(THD *thd, const char *wild)
-{
-  char buff[8192];
-  String packet2(buff,sizeof(buff),thd->charset());
-  List<Item> field_list;
-  CHARSET_INFO **cs;
-  Protocol *protocol= thd->protocol;
-
-  DBUG_ENTER("mysqld_show_charsets");
-
-  field_list.push_back(new Item_empty_string("Collation",30));
-  field_list.push_back(new Item_empty_string("Charset",30));
-  field_list.push_back(new Item_return_int("Id",11, FIELD_TYPE_SHORT));
-  field_list.push_back(new Item_empty_string("Default",30));
-  field_list.push_back(new Item_empty_string("Compiled",30));
-  field_list.push_back(new Item_return_int("Sortlen",3, FIELD_TYPE_SHORT));
-
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-
-  for ( cs= all_charsets ; cs < all_charsets+255 ; cs++ )
-  {
-    CHARSET_INFO **cl;
-    if (!cs[0] || !(cs[0]->state & MY_CS_AVAILABLE) || 
-        !(cs[0]->state & MY_CS_PRIMARY))
-      continue;
-    for ( cl= all_charsets; cl < all_charsets+255 ;cl ++)
-    {
-      if (!cl[0] || !(cl[0]->state & MY_CS_AVAILABLE) || 
-          !my_charset_same(cs[0],cl[0]))
-	continue;
-      if (!(wild && wild[0] &&
-	  wild_case_compare(system_charset_info,cl[0]->name,wild)))
-      {
-        if (write_collation(protocol, cl[0]))
-	  goto err;
-      }
-    }
-  }
-  send_eof(thd); 
-  DBUG_RETURN(0);
-err:
-  DBUG_RETURN(1);
-}
-
-static bool write_charset(Protocol *protocol, CHARSET_INFO *cs)
-{
-  protocol->prepare_for_resend();
-  protocol->store(cs->csname, system_charset_info);
-  protocol->store(cs->comment ? cs->comment : "", system_charset_info);
-  protocol->store(cs->name, system_charset_info);
-  protocol->store_short((longlong) cs->mbmaxlen);
-  return protocol->write();
-}
-
-int mysqld_show_charsets(THD *thd, const char *wild)
-{
-  char buff[8192];
-  String packet2(buff,sizeof(buff),thd->charset());
-  List<Item> field_list;
-  CHARSET_INFO **cs;
-  Protocol *protocol= thd->protocol;
-
-  DBUG_ENTER("mysqld_show_charsets");
-
-  field_list.push_back(new Item_empty_string("Charset",30));
-  field_list.push_back(new Item_empty_string("Description",60));
-  field_list.push_back(new Item_empty_string("Default collation",60));
-  field_list.push_back(new Item_return_int("Maxlen",3, FIELD_TYPE_SHORT));
-
-  if (protocol->send_fields(&field_list,
-                            Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
-    DBUG_RETURN(1);
-
-  for ( cs= all_charsets ; cs < all_charsets+255 ; cs++ )
-  {
-    if (cs[0] && (cs[0]->state & MY_CS_PRIMARY) && 
-        (cs[0]->state & MY_CS_AVAILABLE) &&
-        !(wild && wild[0] &&
-        wild_case_compare(system_charset_info,cs[0]->csname,wild)))
-    {
-      if (write_charset(protocol, cs[0]))
-	goto err;
-    }
-  }
-  send_eof(thd); 
-  DBUG_RETURN(0);
-err:
-  DBUG_RETURN(1);
-}
-
-  
-
 int mysqld_show(THD *thd, const char *wild, show_var_st *variables,
 		enum enum_var_type value_type,
 		pthread_mutex_t *mutex,
@@ -2261,6 +1741,1662 @@ void calc_sum_of_all_status(STATUS_VAR *to)
 }
 
 
+LEX_STRING *make_lex_string(THD *thd, LEX_STRING *lex_str,
+                            const char* str, uint length,
+                            bool allocate_lex_string)
+{
+  MEM_ROOT *mem= thd->mem_root;
+  if (allocate_lex_string)
+    lex_str= (LEX_STRING *)thd->alloc(sizeof(LEX_STRING));
+  lex_str->str= strmake_root(mem, str, length);
+  lex_str->length= length;
+  return lex_str;
+}
+
+
+/* INFORMATION_SCHEMA name */
+LEX_STRING information_schema_name= {(char*)"information_schema", 18};
+extern ST_SCHEMA_TABLE schema_tables[];
+
+typedef struct st_index_field_values
+{
+  const char *db_value, *table_value;
+} INDEX_FIELD_VALUES;
+
+
+void get_index_field_values(LEX *lex, INDEX_FIELD_VALUES *index_field_values)
+{
+  const char *wild= lex->wild ? lex->wild->ptr() : NullS;
+  switch (lex->orig_sql_command) {
+  case SQLCOM_SHOW_DATABASES:
+    index_field_values->db_value= wild;
+    break;
+  case SQLCOM_SHOW_TABLES:
+  case SQLCOM_SHOW_TABLE_STATUS:
+    index_field_values->db_value= lex->current_select->db;
+    index_field_values->table_value= wild;
+    break;
+  default:
+    index_field_values->db_value= NullS;
+    index_field_values->table_value= NullS;
+    break;
+  }
+}
+
+
+int make_table_list(THD *thd, SELECT_LEX *sel,
+                    char *db, char *table)
+{
+  Table_ident *table_ident;
+  LEX_STRING ident_db, ident_table;
+  ident_db.str= db; 
+  ident_db.length= strlen(db);
+  ident_table.str= table;
+  ident_table.length= strlen(table);
+  table_ident= new Table_ident(thd, ident_db, ident_table, 1);
+  sel->init_query();
+  if(!sel->add_table_to_list(thd, table_ident, 0, 0, TL_READ,
+                             (List<String> *) 0, (List<String> *) 0))
+    return 1;
+  return 0;
+}
+
+
+bool uses_only_table_name_fields(Item *item, TABLE_LIST *table)
+{
+  if (item->type() == Item::FUNC_ITEM)
+  {
+    Item_func *item_func= (Item_func*)item;
+    Item **child;
+    Item **item_end= (item_func->arguments()) + item_func->argument_count();
+    for (child= item_func->arguments(); child != item_end; child++)
+      if (!uses_only_table_name_fields(*child, table))
+        return 0;
+    return 1;
+  }
+  else if (item->type() == Item::FIELD_ITEM)
+  {
+    Item_field *item_field= (Item_field*)item;
+    CHARSET_INFO *cs= system_charset_info;
+    ST_SCHEMA_TABLE *schema_table= table->schema_table;
+    ST_FIELD_INFO *field_info= schema_table->fields_info;
+    const char *field_name1= field_info[schema_table->idx_field1].field_name;
+    const char *field_name2= field_info[schema_table->idx_field2].field_name;
+    if(table->table != item_field->field->table ||
+       (cs->coll->strnncollsp(cs, (uchar *) field_name1, strlen(field_name1),
+                              (uchar *) item_field->field_name, 
+                              strlen(item_field->field_name)) &&
+        cs->coll->strnncollsp(cs, (uchar *) field_name2, strlen(field_name2),
+                              (uchar *) item_field->field_name, 
+                              strlen(item_field->field_name))))
+      return 0;
+    else
+      return 1;
+  }
+  else
+    return 1;
+}
+
+
+static COND * make_cond_for_info_schema(COND *cond, TABLE_LIST *table)
+{
+  if (!cond)
+    return (COND*) 0;
+  if (cond->type() == Item::COND_ITEM)
+  {
+    if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
+    {
+      /* Create new top level AND item */
+      Item_cond_and *new_cond=new Item_cond_and;
+      if (!new_cond)
+	return (COND*) 0;
+      List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+      Item *item;
+      while ((item=li++))
+      {
+	Item *fix= make_cond_for_info_schema(item, table);
+	if (fix)
+	  new_cond->argument_list()->push_back(fix);
+      }
+      switch (new_cond->argument_list()->elements) {
+      case 0:
+	return (COND*) 0;
+      case 1:
+	return new_cond->argument_list()->head();
+      default:
+	new_cond->quick_fix_field();
+	return new_cond;
+      }
+    }
+    else
+    {						// Or list
+      Item_cond_or *new_cond=new Item_cond_or;
+      if (!new_cond)
+	return (COND*) 0;
+      List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+      Item *item;
+      while ((item=li++))
+      {
+	Item *fix=make_cond_for_info_schema(item, table);
+	if (!fix)
+	  return (COND*) 0;
+	new_cond->argument_list()->push_back(fix);
+      }
+      new_cond->quick_fix_field();
+      new_cond->top_level_item();
+      return new_cond;
+    }
+  }
+
+  if (!uses_only_table_name_fields(cond, table))
+    return (COND*) 0;
+  return cond;
+}
+
+
+int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  LEX *lex= thd->lex;
+  TABLE *table= tables->table;
+  SELECT_LEX *select_lex= &lex->select_lex;
+  ST_SCHEMA_TABLE *schema_table= tables->schema_table;
+  DBUG_ENTER("fill_schema_tables");
+
+  SELECT_LEX *lsel= tables->schema_select_lex;
+  if (lsel)
+  {
+    TABLE *old_open_tables= thd->open_tables;
+    TABLE_LIST *show_table_list= (TABLE_LIST*) lsel->table_list.first;
+    lex->all_selects_list= lsel;
+    int res= open_and_lock_tables(thd, show_table_list);
+    if (schema_table->process_table(thd, show_table_list,
+                                    table, res, show_table_list->db,
+                                    show_table_list->real_name))
+    {
+      DBUG_RETURN(1);
+    }
+    close_thread_tables(thd, 0, 0, old_open_tables);
+    show_table_list->table= 0;
+    lex->all_selects_list= select_lex;
+    DBUG_RETURN(0);
+  }
+
+  SELECT_LEX sel;
+  INDEX_FIELD_VALUES idx_field_vals;
+  char path[FN_REFLEN], *end, *base_name, *file_name;
+  uint len;
+  List<char> bases;
+  lex->all_selects_list= &sel;
+  enum enum_schema_tables schema_table_idx=
+    (enum enum_schema_tables) (schema_table - &schema_tables[0]);
+  thr_lock_type lock_type= TL_UNLOCK;
+  if (schema_table_idx == SCH_TABLES)
+    lock_type= TL_READ;
+  get_index_field_values(lex, &idx_field_vals);
+  if (mysql_find_files(thd, &bases, NullS, mysql_data_home,
+		       idx_field_vals.db_value, 1))
+    return 1;
+  List_iterator_fast<char> it(bases);
+  COND *partial_cond= make_cond_for_info_schema(cond, tables);
+  while ((base_name=it++) ||
+	 /*
+	   generate error for non existing database.
+	   (to save old behaviour for SHOW TABLES FROM db)
+	 */
+	 ((lex->orig_sql_command == SQLCOM_SHOW_TABLES ||
+           lex->orig_sql_command == SQLCOM_SHOW_TABLE_STATUS) &&
+	  (base_name= select_lex->db) && !bases.elements))
+  {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    if (!check_access(thd,SELECT_ACL, base_name, &thd->col_access,0,1) ||
+        thd->master_access & (DB_ACLS | SHOW_DB_ACL) ||
+	acl_get(thd->host, thd->ip, thd->priv_user, base_name,0) ||
+	(grant_option && !check_grant_db(thd, base_name)))
+#endif
+    {
+      List<char> files;
+      (void) sprintf(path,"%s/%s",mysql_data_home,base_name);
+      end= path + (len= unpack_dirname(path,path));
+      len= FN_LEN - len;
+      if (mysql_find_files(thd, &files, base_name, 
+                           path, idx_field_vals.table_value, 0))
+	DBUG_RETURN(1);
+
+      List_iterator_fast<char> it(files);
+      while ((file_name=it++))
+      {
+	restore_record(table, default_values);
+        table->field[schema_table->idx_field1]->
+          store(base_name, strlen(base_name), system_charset_info);
+        table->field[schema_table->idx_field2]->
+          store(file_name, strlen(file_name),system_charset_info);
+        if (!partial_cond || partial_cond->val_int())
+        {
+          if (schema_table_idx == SCH_TABLE_NAMES)
+          {
+            if (lex->verbose || lex->orig_sql_command == SQLCOM_END)
+            {
+              my_snprintf(end, len, "/%s%s", file_name, reg_ext);
+              switch (mysql_frm_type(path))
+              {
+              case FRMTYPE_ERROR:
+                table->field[3]->store("ERROR", 5, system_charset_info);
+                break;
+              case FRMTYPE_TABLE:
+                table->field[3]->store("BASE TABLE", 10, system_charset_info);
+                break;
+              case FRMTYPE_VIEW:
+                table->field[3]->store("VIEW", 4, system_charset_info);
+                break;
+              default:
+                DBUG_ASSERT(0);
+              }
+            }
+            table->file->write_row(table->record[0]);
+          }
+          else
+          {
+            int res;
+            TABLE *old_open_tables= thd->open_tables;
+            if (make_table_list(thd, &sel, base_name, file_name))
+              DBUG_RETURN(1);
+            TABLE_LIST *show_table_list= (TABLE_LIST*) sel.table_list.first;
+            show_table_list->lock_type= lock_type;
+            res= open_and_lock_tables(thd, show_table_list);
+            if (schema_table->process_table(thd, show_table_list, table,
+                                            res, base_name, file_name))
+            {
+              DBUG_RETURN(1);
+            }
+            close_thread_tables(thd, 0, 0, old_open_tables);
+          }
+        }
+      }
+    }
+  }
+  lex->all_selects_list= select_lex;
+  DBUG_RETURN(0);
+}
+
+
+int fill_schema_shemata(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  char path[FN_REFLEN],*end;
+  bool found_libchar;
+  INDEX_FIELD_VALUES idx_field_vals;
+  List<char> files;
+  char *file_name;
+  uint length;
+  HA_CREATE_INFO create;
+  TABLE *table= tables->table;
+
+  get_index_field_values(thd->lex, &idx_field_vals);
+  if (mysql_find_files(thd, &files, NullS, mysql_data_home,
+                       idx_field_vals.db_value, 1))
+    return 1;
+  List_iterator_fast<char> it(files);
+  while ((file_name=it++))
+  {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    if (thd->master_access & (DB_ACLS | SHOW_DB_ACL) ||
+	acl_get(thd->host, thd->ip, thd->priv_user, file_name,0) ||
+	(grant_option && !check_grant_db(thd, file_name)))
+#endif
+    {
+      (void) sprintf(path,"%s/%s",mysql_data_home, file_name);
+      length=unpack_dirname(path,path);		// Convert if not unix
+      found_libchar= 0;
+      if (length && path[length-1] == FN_LIBCHAR)
+      {
+	found_libchar= 1;
+	path[length-1]=0;			// remove ending '\'
+      }
+
+      if (found_libchar)
+	path[length-1]= FN_LIBCHAR;
+      strmov(path+length, MY_DB_OPT_FILE);
+      load_db_opt(thd, path, &create);
+      restore_record(table, default_values);
+      table->field[1]->store(file_name, strlen(file_name), system_charset_info);
+      table->field[2]->store(create.default_table_charset->name, 
+			     strlen(create.default_table_charset->name),
+			     system_charset_info);
+      table->file->write_row(table->record[0]);
+    }
+  }
+  return 0;
+}
+
+
+int get_schema_tables_record(THD *thd, struct st_table_list *tables,
+                             TABLE *table, int res, 
+                             const char *base_name, const char *file_name)
+{
+  const char *tmp_buff;
+  TIME time;
+  CHARSET_INFO *cs= system_charset_info;
+
+  DBUG_ENTER("get_schema_tables_record");
+  restore_record(table, default_values);
+  if (res > 0)
+  {
+    DBUG_RETURN(1);
+  }
+  table->field[1]->store(base_name, strlen(base_name), cs);
+  table->field[2]->store(file_name, strlen(file_name), cs);
+  if (res < 0 || tables->view)
+  {
+    table->field[3]->store("VIEW", 4, cs);
+    table->field[20]->store("view", 4, cs);
+    if (res)
+      thd->clear_error();
+  }
+  else
+  {
+    TABLE *show_table= tables->table;
+    handler *file= show_table->file;
+    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
+    table->field[3]->store("BASE TABLE", 10, cs);
+    for (int i= 4; i < 20; i++)
+    {
+      if ((i > 12 && i < 17) || i == 18)
+        continue;
+      table->field[i]->set_notnull();
+    }
+    tmp_buff= file->table_type();
+    table->field[4]->store(tmp_buff, strlen(tmp_buff), cs);
+    table->field[5]->store((longlong) show_table->frm_version);
+    tmp_buff= ((show_table->db_options_in_use &
+                HA_OPTION_COMPRESS_RECORD) ? "Compressed" :
+               (show_table->db_options_in_use & HA_OPTION_PACK_RECORD) ?
+               "Dynamic" : "Fixed");
+    table->field[6]->store(tmp_buff, strlen(tmp_buff), cs);
+    table->field[7]->store((longlong) file->records);
+    table->field[8]->store((longlong) file->mean_rec_length);
+    table->field[9]->store((longlong) file->data_file_length);
+    if (file->max_data_file_length)
+    {
+      table->field[10]->store((longlong) file->max_data_file_length);
+    }
+    table->field[11]->store((longlong) file->index_file_length);
+    table->field[12]->store((longlong) file->delete_length);
+    if (table->found_next_number_field)
+    {
+      show_table->next_number_field=show_table->found_next_number_field;
+      show_table->next_number_field->reset();
+      file->update_auto_increment();
+      table->field[13]->store((longlong) show_table->
+                              next_number_field->val_int());
+      table->field[13]->set_notnull();
+      show_table->next_number_field=0;
+    }
+    if (file->create_time)
+    {
+      thd->variables.time_zone->gmt_sec_to_TIME(&time,
+                                                file->create_time);
+      table->field[14]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[14]->set_notnull();
+    }
+    if (file->update_time)
+    {
+      thd->variables.time_zone->gmt_sec_to_TIME(&time,
+                                                file->update_time);
+      table->field[15]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[15]->set_notnull();
+    }
+    if (file->check_time)
+    {
+      thd->variables.time_zone->gmt_sec_to_TIME(&time, file->check_time);
+      table->field[16]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[16]->set_notnull();
+    }
+    tmp_buff= (show_table->table_charset ? show_table->
+               table_charset->name : "default");
+    table->field[17]->store(tmp_buff, strlen(tmp_buff), cs);
+    if (file->table_flags() & HA_HAS_CHECKSUM)
+    {
+      table->field[18]->store((longlong) file->checksum());
+      table->field[18]->set_notnull();
+    }
+
+    char option_buff[350],*ptr;
+    ptr=option_buff;
+    if (show_table->min_rows)
+    {
+      ptr=strmov(ptr," min_rows=");
+      ptr=longlong10_to_str(show_table->min_rows,ptr,10);
+    }
+    if (show_table->max_rows)
+    {
+      ptr=strmov(ptr," max_rows=");
+      ptr=longlong10_to_str(show_table->max_rows,ptr,10);
+    }
+    if (show_table->avg_row_length)
+    {
+      ptr=strmov(ptr," avg_row_length=");
+      ptr=longlong10_to_str(show_table->avg_row_length,ptr,10);
+    }
+    if (show_table->db_create_options & HA_OPTION_PACK_KEYS)
+      ptr=strmov(ptr," pack_keys=1");
+    if (show_table->db_create_options & HA_OPTION_NO_PACK_KEYS)
+      ptr=strmov(ptr," pack_keys=0");
+    if (show_table->db_create_options & HA_OPTION_CHECKSUM)
+      ptr=strmov(ptr," checksum=1");
+    if (show_table->db_create_options & HA_OPTION_DELAY_KEY_WRITE)
+      ptr=strmov(ptr," delay_key_write=1");
+    if (show_table->row_type != ROW_TYPE_DEFAULT)
+      ptr=strxmov(ptr, " row_format=", 
+                  ha_row_type[(uint) show_table->row_type],
+                  NullS);
+    if (file->raid_type)
+    {
+      char buff[100];
+      sprintf(buff," raid_type=%s raid_chunks=%d raid_chunksize=%ld",
+              my_raid_type(file->raid_type), file->raid_chunks,
+              file->raid_chunksize/RAID_BLOCK_SIZE);
+      ptr=strmov(ptr,buff);
+    }
+    table->field[19]->store(option_buff+1,
+                            (ptr == option_buff ? 0 : 
+                             (uint) (ptr-option_buff)-1), cs);
+
+    char *comment= show_table->file->
+      update_table_comment(show_table->comment);
+    table->field[20]->store(comment, strlen(comment), cs);
+    if (comment != show_table->comment)
+      my_free(comment,MYF(0));
+  }
+  table->file->write_row(table->record[0]);
+  DBUG_RETURN(0);
+}
+
+
+int get_schema_column_record(THD *thd, struct st_table_list *tables,
+                             TABLE *table, int res, 
+                             const char *base_name, const char *file_name)
+{
+  TIME time;
+  const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
+  CHARSET_INFO *cs= system_charset_info;
+  DBUG_ENTER("get_schema_column_record");
+  if (res)
+  {
+    DBUG_RETURN(1);
+  }
+
+  TABLE *show_table= tables->table;
+  handler *file= show_table->file;
+  file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
+  restore_record(show_table, default_values);
+  Field **ptr,*field;
+  int count= 0;
+  for (ptr=show_table->field; (field= *ptr) ; ptr++)
+  {
+    if (!wild || !wild[0] || 
+        !wild_case_compare(system_charset_info, field->field_name,wild))
+    {
+      uint tmp_length;
+      char *tmp_buff;
+      byte *pos;
+      uint flags=field->flags;
+      char tmp[MAX_FIELD_WIDTH];
+      char tmp1[MAX_FIELD_WIDTH];
+      String type(tmp,sizeof(tmp), system_charset_info);
+      char tmp_buffer[128];
+      count++;
+      restore_record(table, default_values);
+      table->field[1]->store(base_name, strlen(base_name), cs);
+      table->field[2]->store(file_name, strlen(file_name), cs);
+      table->field[3]->store(field->field_name, strlen(field->field_name),
+                             cs);
+      table->field[4]->store((longlong) count);
+      field->sql_type(type);
+      table->field[11]->store(type.ptr(), type.length(), cs);		
+      tmp_buff= strchr(type.ptr(),'(');
+      table->field[5]->store(type.ptr(),
+                             (tmp_buff ? tmp_buff - type.ptr() :
+                              type.length()), cs);
+
+      if (show_table->timestamp_field == field &&
+          field->unireg_check != Field::TIMESTAMP_UN_FIELD)
+      {
+        table->field[15]->store("CURRENT_TIMESTAMP", 17, cs);
+        table->field[15]->set_notnull();
+      }
+      else if (field->unireg_check != Field::NEXT_NUMBER &&
+               !field->is_null() &&
+               !(field->flags & NO_DEFAULT_VALUE_FLAG))
+      {
+        String def(tmp1,sizeof(tmp1), cs);
+        type.set(tmp, sizeof(tmp), field->charset());
+        field->val_str(&type);
+        uint dummy_errors;
+        def.copy(type.ptr(), type.length(), type.charset(), cs, &dummy_errors);
+        table->field[15]->store(def.ptr(), def.length(), def.charset());
+        table->field[15]->set_notnull();
+      }
+      else if (field->unireg_check == Field::NEXT_NUMBER ||
+               field->maybe_null())
+        table->field[15]->set_null();                // Null as default
+      else
+      {
+        table->field[15]->store("",0, cs);
+        table->field[15]->set_notnull();
+      }
+		
+      pos=(byte*) ((flags & NOT_NULL_FLAG) &&
+                   field->type() != FIELD_TYPE_TIMESTAMP ?
+                   "" : "YES");
+      table->field[13]->store((const char*) pos,
+                              strlen((const char*) pos), cs);
+      if (field->has_charset())
+      {
+        table->field[6]->store((longlong) field->field_length/
+                               field->charset()->mbmaxlen);
+      }
+      table->field[7]->store((longlong) field->field_length); 
+      table->field[8]->store((longlong) field->pack_length());
+      table->field[9]->store((longlong) field->decimals());
+      if (field->has_charset())
+      {
+        pos=(byte*) field->charset()->csname;
+        table->field[10]->store((const char*) pos,
+                                strlen((const char*) pos), cs);
+        table->field[10]->set_notnull();
+        pos=(byte*) field->charset()->name;
+        table->field[12]->store((const char*) pos,
+                                strlen((const char*) pos), cs);
+        table->field[12]->set_notnull();
+      }
+      pos=(byte*) ((field->flags & PRI_KEY_FLAG) ? "PRI" :
+                   (field->flags & UNIQUE_KEY_FLAG) ? "UNI" :
+                   (field->flags & MULTIPLE_KEY_FLAG) ? "MUL":"");
+      table->field[14]->store((const char*) pos,
+                              strlen((const char*) pos), cs);
+      char *end=tmp;
+      if (field->unireg_check == Field::NEXT_NUMBER)
+        end=strmov(tmp,"auto_increment");
+      table->field[16]->store(tmp, (uint) (end-tmp), cs);
+      if (thd->lex->verbose)
+      {
+        end=tmp;
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+        uint col_access;
+        check_access(thd,SELECT_ACL | EXTRA_ACL, base_name,
+                     &tables->grant.privilege, 0, 0);
+        col_access= get_column_grant(thd, &tables->grant, tables->db,
+                                     tables->real_name,
+                                     field->field_name) & COL_ACLS;
+        for (uint bitnr=0; col_access ; col_access>>=1,bitnr++)
+        {
+          if (col_access & 1)
+          {
+            *end++=',';
+            end=strmov(end,grant_types.type_names[bitnr]);
+          }
+        }
+#else
+        end=strmov(end,"");
+#endif
+        table->field[17]->store(tmp+1,end == tmp ? 0 : (uint) (end-tmp-1), cs);
+        table->field[18]->store(field->comment.str, field->comment.length, cs);
+      }
+      table->file->write_row(table->record[0]);
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+
+int fill_schema_charsets(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  CHARSET_INFO **cs;
+  const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
+  TABLE *table= tables->table;
+  CHARSET_INFO *scs= system_charset_info;
+  for ( cs= all_charsets ; cs < all_charsets+255 ; cs++ )
+  {
+    CHARSET_INFO *tmp_cs= cs[0];
+    if (tmp_cs && (tmp_cs->state & MY_CS_PRIMARY) && 
+        (tmp_cs->state & MY_CS_AVAILABLE) &&
+        !(wild && wild[0] &&
+	  wild_case_compare(scs, tmp_cs->csname,wild)))
+    {
+      restore_record(table, default_values);
+      table->field[0]->store(tmp_cs->csname, strlen(tmp_cs->csname), scs);
+      table->field[1]->store(tmp_cs->comment ? tmp_cs->comment : "",
+			     strlen(tmp_cs->comment ? tmp_cs->comment : ""),
+                             scs);
+      table->field[2]->store(tmp_cs->name, strlen(tmp_cs->name), scs);
+      table->field[3]->store((longlong) tmp_cs->mbmaxlen);
+      table->file->write_row(table->record[0]);
+    }
+  }
+  return 0;
+}
+
+
+int fill_schema_collation(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  CHARSET_INFO **cs;
+  const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
+  TABLE *table= tables->table;
+  CHARSET_INFO *scs= system_charset_info;
+  for ( cs= all_charsets ; cs < all_charsets+255 ; cs++ )
+  {
+    CHARSET_INFO **cl;
+    CHARSET_INFO *tmp_cs= cs[0];
+    if (!tmp_cs || !(tmp_cs->state & MY_CS_AVAILABLE) || 
+        !(tmp_cs->state & MY_CS_PRIMARY))
+      continue;
+    for ( cl= all_charsets; cl < all_charsets+255 ;cl ++)
+    {
+      CHARSET_INFO *tmp_cl= cl[0];
+      if (!tmp_cl || !(tmp_cl->state & MY_CS_AVAILABLE) || 
+          !my_charset_same(tmp_cs, tmp_cl))
+	continue;
+      if (!(wild && wild[0] &&
+	  wild_case_compare(scs, tmp_cl->name,wild)))
+      {
+	const char *tmp_buff;
+	restore_record(table, default_values);
+	table->field[0]->store(tmp_cl->name, strlen(tmp_cl->name), scs);
+        table->field[1]->store(tmp_cl->csname , strlen(tmp_cl->csname), scs);
+        table->field[2]->store((longlong) tmp_cl->number);
+        tmp_buff= (tmp_cl->state & MY_CS_PRIMARY) ? "Yes" : "";
+	table->field[3]->store(tmp_buff, strlen(tmp_buff), scs);
+        tmp_buff= (tmp_cl->state & MY_CS_COMPILED)? "Yes" : "";
+	table->field[4]->store(tmp_buff, strlen(tmp_buff), scs);
+        table->field[5]->store((longlong) tmp_cl->strxfrm_multiply);
+	table->file->write_row(table->record[0]);
+      }
+    }
+  }
+  return 0;
+}
+
+
+int fill_schema_coll_charset_app(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  CHARSET_INFO **cs;
+  const char *wild= NullS;
+  TABLE *table= tables->table;
+  CHARSET_INFO *scs= system_charset_info;
+  for ( cs= all_charsets ; cs < all_charsets+255 ; cs++ )
+  {
+    CHARSET_INFO **cl;
+    CHARSET_INFO *tmp_cs= cs[0];
+    if (!tmp_cs || !(tmp_cs->state & MY_CS_AVAILABLE) || 
+        !(tmp_cs->state & MY_CS_PRIMARY))
+      continue;
+    for ( cl= all_charsets; cl < all_charsets+255 ;cl ++)
+    {
+      CHARSET_INFO *tmp_cl= cl[0];
+      if (!tmp_cl || !(tmp_cl->state & MY_CS_AVAILABLE) || 
+          !my_charset_same(tmp_cs,tmp_cl))
+	continue;
+      restore_record(table, default_values);
+      table->field[0]->store(tmp_cl->name, strlen(tmp_cl->name), scs);
+      table->field[1]->store(tmp_cl->csname , strlen(tmp_cl->csname), scs);
+      table->file->write_row(table->record[0]);
+    }
+  }
+  return 0;
+}
+
+
+void store_schema_proc(THD *thd, TABLE *table,
+		       TABLE *proc_table,
+		       const char *wild)
+{
+  String tmp_string;
+  TIME time;
+  LEX *lex= thd->lex;
+  CHARSET_INFO *cs= system_charset_info;
+  restore_record(table, default_values);
+  if (lex->orig_sql_command == SQLCOM_SHOW_STATUS_PROC &&
+      proc_table->field[2]->val_int() == TYPE_ENUM_PROCEDURE ||
+      lex->orig_sql_command == SQLCOM_SHOW_STATUS_FUNC &&
+      proc_table->field[2]->val_int() == TYPE_ENUM_FUNCTION ||
+      lex->orig_sql_command == SQLCOM_END)
+  {
+    tmp_string.length(0);
+    get_field(thd->mem_root, proc_table->field[1], &tmp_string);
+    if (!wild || !wild[0] || !wild_compare(tmp_string.ptr(), wild, 0))
+    {
+      table->field[3]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[0], &tmp_string);
+      table->field[2]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[2], &tmp_string);
+      table->field[4]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[3], &tmp_string);
+      table->field[0]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[5], &tmp_string);
+      table->field[11]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[6], &tmp_string);
+      table->field[10]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[7], &tmp_string);
+      table->field[15]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[9], &tmp_string);
+      table->field[6]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[10], &tmp_string);
+      table->field[8]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[11], &tmp_string);
+      table->field[5]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      bzero((char *)&time, sizeof(time));
+      ((Field_timestamp *) proc_table->field[12])->get_time(&time);
+      table->field[14]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      bzero((char *)&time, sizeof(time));
+      ((Field_timestamp *) proc_table->field[13])->get_time(&time);
+      table->field[13]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      get_field(thd->mem_root, proc_table->field[14], &tmp_string);
+      table->field[16]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      tmp_string.length(0);
+      get_field(thd->mem_root, proc_table->field[15], &tmp_string);
+      table->field[17]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      table->field[7]->store("SQL", 3, cs);
+      table->field[9]->store("SQL", 3, cs);
+      table->file->write_row(table->record[0]);
+    }
+  }
+}
+
+
+int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  TABLE *proc_table;
+  TABLE_LIST proc_tables;
+  const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
+  int res= 0;
+  TABLE *table= tables->table, *old_open_tables= thd->open_tables;
+  DBUG_ENTER("fill_schema_proc");
+
+  bzero((char*) &proc_tables,sizeof(proc_tables));
+  proc_tables.db= (char*) "mysql";
+  proc_tables.real_name= proc_tables.alias= (char*) "proc";
+  proc_tables.lock_type= TL_READ;
+  if (!(proc_table= open_ltable(thd, &proc_tables, TL_READ)))
+  {
+    DBUG_RETURN(1);
+  }
+  proc_table->file->ha_index_init(0);
+  if ((res= proc_table->file->index_first(proc_table->record[0])))
+  {
+    res= (res == HA_ERR_END_OF_FILE) ? 0 : 1;
+    goto err;
+  }
+  store_schema_proc(thd, table, proc_table, wild);
+  while (!proc_table->file->index_next(proc_table->record[0]))
+    store_schema_proc(thd, table, proc_table, wild);
+
+err:
+  proc_table->file->ha_index_end();
+  close_thread_tables(thd, 0, 0, old_open_tables);
+  DBUG_RETURN(res);
+}
+
+
+int get_schema_stat_record(THD *thd, struct st_table_list *tables,
+                           TABLE *table, int res, 
+                           const char *base_name, const char *file_name)
+{
+  CHARSET_INFO *cs= system_charset_info;
+  DBUG_ENTER("get_schema_stat_record");
+  if (!res)
+  {
+    TABLE *show_table= tables->table;
+    KEY *key_info=show_table->key_info;
+    show_table->file->info(HA_STATUS_VARIABLE | 
+                           HA_STATUS_NO_LOCK |
+                           HA_STATUS_TIME);
+    for (uint i=0 ; i < show_table->keys ; i++,key_info++)
+    {
+      KEY_PART_INFO *key_part= key_info->key_part;
+      const char *str;
+      for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+      {
+        restore_record(table, default_values);
+        table->field[1]->store(base_name, strlen(base_name), cs);
+        table->field[2]->store(file_name, strlen(file_name), cs);
+        table->field[3]->store((longlong) ((key_info->flags & 
+                                            HA_NOSAME) ? 0 :1));
+        table->field[4]->store(base_name, strlen(base_name), cs);
+        table->field[5]->store(key_info->name, strlen(key_info->name), cs);
+        table->field[6]->store((longlong) (j+1));
+        str=(key_part->field ? key_part->field->field_name :
+             "?unknown field?");
+        table->field[7]->store(str, strlen(str), cs);
+        if (show_table->file->index_flags(i, j, 0) & HA_READ_ORDER)
+        {
+          table->field[8]->store(((key_part->key_part_flag &
+                                   HA_REVERSE_SORT) ?
+                                  "D" : "A"), 1, cs);
+          table->field[8]->set_notnull();
+        }
+        KEY *key=show_table->key_info+i;
+        if (key->rec_per_key[j])
+        {
+          ha_rows records=(show_table->file->records /
+                           key->rec_per_key[j]);
+          table->field[9]->store((longlong) records);
+          table->field[9]->set_notnull();
+        }
+        if (!(key_info->flags & HA_FULLTEXT) && 
+            (!key_part->field ||
+             key_part->length != 
+             show_table->field[key_part->fieldnr-1]->key_length()))
+        {
+          table->field[10]->store((longlong) key_part->length);
+          table->field[10]->set_notnull();
+        }
+        uint flags= key_part->field ? key_part->field->flags : 0;
+        const char *pos=(char*) ((flags & NOT_NULL_FLAG) ? "" : "YES");
+        table->field[12]->store(pos, strlen(pos), cs);
+        pos= show_table->file->index_type(i);
+        table->field[13]->store(pos, strlen(pos), cs);
+        if (!show_table->keys_in_use.is_set(i))
+          table->field[14]->store("disabled", 8, cs);
+        else
+          table->field[14]->store("", 0, cs);
+        table->field[14]->set_notnull();
+        table->file->write_row(table->record[0]);
+      }
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+int get_schema_views_record(THD *thd, struct st_table_list *tables,
+                            TABLE *table, int res, 
+                            const char *base_name, const char *file_name)
+{
+  CHARSET_INFO *cs= system_charset_info;
+  DBUG_ENTER("get_schema_views_record");
+  if (!res)
+  {
+    if (tables->view)
+    {
+      restore_record(table, default_values);
+      table->field[1]->store(tables->view_db.str, tables->view_db.length, cs);
+      table->field[2]->store(tables->view_name.str,tables->view_name.length,cs);
+      table->field[3]->store(tables->query.str, tables->query.length, cs);
+      table->field[4]->store("NONE", 4, cs);
+      if (tables->updatable_view)
+        table->field[5]->store("YES", 3, cs);
+      else
+        table->field[5]->store("NO", 2, cs);
+      table->file->write_row(table->record[0]);
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+int get_schema_constarints_record(THD *thd, struct st_table_list *tables,
+                                  TABLE *table, int res, 
+                                  const char *base_name, const char *file_name)
+{
+  CHARSET_INFO *cs= system_charset_info;
+  DBUG_ENTER("get_schema_constarints_record");
+  if (!res)
+  {
+    List<FOREIGN_KEY_INFO> f_key_list;
+    TABLE *show_table= tables->table;
+    KEY *key_info=show_table->key_info;
+    uint primary_key= show_table->primary_key;
+    show_table->file->info(HA_STATUS_VARIABLE | 
+                           HA_STATUS_NO_LOCK |
+                           HA_STATUS_TIME);
+    for (uint i=0 ; i < show_table->keys ; i++, key_info++)
+    {
+      if (i != primary_key && !(key_info->flags & HA_NOSAME))
+        continue;              
+      restore_record(table, default_values);
+      table->field[1]->store(base_name, strlen(base_name), cs);
+      table->field[2]->store(key_info->name, strlen(key_info->name), cs);
+      table->field[3]->store(base_name, strlen(base_name), cs);
+      table->field[4]->store(file_name, strlen(file_name), cs);
+      if (i == primary_key && !strcmp(key_info->name, primary_key_name))
+        table->field[5]->store("PRIMARY", 7, cs);
+      else if (key_info->flags & HA_NOSAME)
+        table->field[5]->store("UNIQUE", 6, cs);
+      table->file->write_row(table->record[0]);
+    }
+
+    show_table->file->get_foreign_key_list(thd, &f_key_list);
+    FOREIGN_KEY_INFO *f_key_info;
+    List_iterator_fast<FOREIGN_KEY_INFO> it(f_key_list);
+    while ((f_key_info=it++))
+    {
+      restore_record(table, default_values);
+      table->field[1]->store(base_name, strlen(base_name), cs);
+      table->field[2]->store(f_key_info->forein_id->str,
+                             f_key_info->forein_id->length, cs);
+      table->field[3]->store(base_name, strlen(base_name), cs);
+      table->field[4]->store(file_name, strlen(file_name), cs);
+      table->field[5]->store("FOREIGN", 7, system_charset_info);
+      table->field[6]->store(f_key_info->constraint_method->str,
+                             f_key_info->constraint_method->length, cs);
+      table->field[6]->set_notnull();
+      table->file->write_row(table->record[0]);
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+int get_schema_key_column_usage_record(THD *thd, struct st_table_list *tables,
+                                       TABLE *table, int res,
+                                       const char *base_name,
+                                       const char *file_name)
+{
+  DBUG_ENTER("get_schema_key_column_usage_record");
+  CHARSET_INFO *cs= system_charset_info;
+  if (!res)
+  {
+    List<FOREIGN_KEY_INFO> f_key_list;
+    TABLE *show_table= tables->table;
+    KEY *key_info=show_table->key_info;
+    uint primary_key= show_table->primary_key;
+    show_table->file->info(HA_STATUS_VARIABLE | 
+                           HA_STATUS_NO_LOCK |
+                           HA_STATUS_TIME);
+    for (uint i=0 ; i < show_table->keys ; i++, key_info++)
+    {
+      if (i != primary_key && !(key_info->flags & HA_NOSAME))
+        continue;              
+      uint f_idx= 0;
+      KEY_PART_INFO *key_part= key_info->key_part;
+      for (uint j=0 ; j < key_info->key_parts ; j++,key_part++)
+      {
+        uint f_idx= 0;
+        if (key_part->field)
+        {
+          f_idx++;
+          restore_record(table, default_values);
+          table->field[1]->store(base_name, strlen(base_name), cs);
+          table->field[2]->store(key_info->name, strlen(key_info->name), cs);
+          table->field[3]->store(base_name, strlen(base_name), cs);
+          table->field[4]->store(file_name, strlen(file_name), cs);
+          table->field[5]->store(key_part->field->field_name, 
+                                 strlen(key_part->field->field_name), cs);
+          table->field[6]->store((longlong) f_idx);
+          table->file->write_row(table->record[0]);
+        }
+      }
+    }
+
+    show_table->file->get_foreign_key_list(thd, &f_key_list);
+    FOREIGN_KEY_INFO *f_key_info;
+    List_iterator_fast<FOREIGN_KEY_INFO> it(f_key_list);
+    while ((f_key_info= it++))
+    {
+      LEX_STRING *f_info, *r_info;
+      List_iterator_fast<LEX_STRING> it(f_key_info->foreign_fields),
+        it1(f_key_info->referenced_fields);
+      uint f_idx= 0;
+      while ((f_info= it++))
+      {
+        r_info= it1++;
+        f_idx++;
+        restore_record(table, default_values);
+        table->field[1]->store(base_name, strlen(base_name), cs);
+        table->field[2]->store(f_key_info->forein_id->str,
+                               f_key_info->forein_id->length, cs);
+        table->field[3]->store(base_name, strlen(base_name), cs);
+        table->field[4]->store(file_name, strlen(file_name), cs);
+        table->field[5]->store(f_info->str, f_info->length, cs);
+        table->field[6]->store((longlong) f_idx);
+        table->field[7]->store(f_key_info->referenced_db->str,
+                               f_key_info->referenced_db->length, cs);
+        table->field[7]->set_notnull();
+        table->field[8]->store(f_key_info->referenced_table->str,
+                               f_key_info->referenced_table->length, cs);
+        table->field[8]->set_notnull();
+        table->field[9]->store(r_info->str, r_info->length, cs);
+        table->field[9]->set_notnull();
+        table->file->write_row(table->record[0]);
+      }
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Find schema_tables elment by name
+
+  SYNOPSIS
+    find_schema_table()
+    thd                 thread handler
+    table_name          table name
+
+  RETURN
+    0	table not found
+    #   pointer to 'shema_tables' element
+*/
+
+ST_SCHEMA_TABLE *find_schema_table(THD *thd, const char* table_name)
+{
+  ST_SCHEMA_TABLE *schema_table= schema_tables;
+  for ( ; schema_table->table_name; schema_table++)
+  {
+    if (!my_strcasecmp(system_charset_info,
+                       schema_table->table_name,
+                       table_name))
+      return schema_table;
+  }
+  return 0;
+}
+
+
+ST_SCHEMA_TABLE *get_schema_table(enum enum_schema_tables schema_table_idx)
+{
+  return &schema_tables[schema_table_idx];
+}
+
+
+/*
+  Create information_schema table using schema_table data
+
+  SYNOPSIS
+    create_schema_table()
+    thd	       	          thread handler
+    schema_table          pointer to 'shema_tables' element
+
+  RETURN
+    #	                  Pointer to created table
+    0	                  Can't create table
+*/
+
+TABLE *create_schema_table(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  int field_count= 0;
+  Item *item;
+  TABLE *table;
+  List<Item> field_list;
+  ST_FIELD_INFO *fields_info= schema_table->fields_info;
+  DBUG_ENTER("create_schema_table");
+
+  for ( ; fields_info->field_name; fields_info++)
+  {
+    switch (fields_info->field_type) {
+    case MYSQL_TYPE_LONG:
+      if (!(item= new Item_int(fields_info->field_name,
+                               fields_info->value,
+                               fields_info->field_length)))
+      {
+        DBUG_RETURN(0);
+      }
+      break;
+    case MYSQL_TYPE_TIMESTAMP:
+      if (!(item=new Item_datetime(fields_info->field_name)))
+      {
+        DBUG_RETURN(0);
+      }
+      break;
+    default:
+      CHARSET_INFO *cs= system_charset_info;
+      if (fields_info->utf8)
+	cs= thd->charset();
+      if (!(item= new Item_string("", fields_info->field_length, cs)))
+      {
+        DBUG_RETURN(0);
+      }
+      item->set_name(fields_info->field_name, 
+		     strlen(fields_info->field_name), cs);
+      break;
+    }
+    field_list.push_back(item);
+    item->maybe_null= fields_info->maybe_null;
+    field_count++;
+  }
+  TMP_TABLE_PARAM *tmp_table_param =
+    (TMP_TABLE_PARAM*) (thd->calloc(sizeof(TMP_TABLE_PARAM)));
+  tmp_table_param->init();
+  tmp_table_param->field_count= field_count;
+  SELECT_LEX *select_lex= thd->lex->current_select;
+  if (!(table= create_tmp_table(thd, tmp_table_param,
+                                field_list, (ORDER*) 0, 0, 0, 
+                                (select_lex->options | thd->options |
+                                 TMP_TABLE_ALL_COLUMNS),
+                                HA_POS_ERROR,
+                                (char *) schema_table->table_name)))
+    DBUG_RETURN(0);
+  DBUG_RETURN(table);
+}
+
+
+/*
+  For old SHOW compatibility. It is used when
+  old SHOW doesn't have generated column names
+  Make list of fields for SHOW
+
+  SYNOPSIS
+    make_old_format()
+    thd			thread handler
+    schema_table        pointer to 'schema_tables' element
+
+  RETURN
+   -1	errror
+    0	success
+*/
+
+int make_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  ST_FIELD_INFO *field_info= schema_table->fields_info;
+  for ( ; field_info->field_name; field_info++)
+  {
+    if (field_info->old_name)
+    {
+      Item_field *field= new Item_field(NullS, NullS, field_info->field_name);
+      if (field)
+      {
+        field->set_name(field_info->old_name,
+                        strlen(field_info->old_name),
+                        system_charset_info);
+        if (add_item_to_list(thd, field))
+          return 1;
+      }
+    }
+  }
+  return 0;
+}
+
+
+int make_schemata_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  char tmp[128];
+  LEX *lex= thd->lex;
+  SELECT_LEX *sel= lex->current_select;
+
+  if (!sel->item_list.elements)
+  {
+    ST_FIELD_INFO *field_info= &schema_table->fields_info[1];
+    String buffer(tmp,sizeof(tmp), system_charset_info);
+    Item_field *field= new Item_field(NullS, NullS, field_info->field_name);
+    if (!field || add_item_to_list(thd, field))
+      return 1;
+    buffer.length(0);
+    buffer.append(field_info->old_name);
+    if (lex->wild && lex->wild->ptr())
+    {
+      buffer.append(" (");
+      buffer.append(lex->wild->ptr());
+      buffer.append(")");
+    }
+    field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
+  }
+  return 0;
+}
+
+
+int make_table_names_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  char tmp[128];
+  String buffer(tmp,sizeof(tmp), thd->charset());
+  LEX *lex= thd->lex;
+
+  ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
+  buffer.length(0);
+  buffer.append(field_info->old_name);
+  buffer.append(lex->select_lex.db);
+  if (lex->wild && lex->wild->ptr())
+  {
+    buffer.append(" (");
+    buffer.append(lex->wild->ptr());
+    buffer.append(")");
+  }
+  Item_field *field= new Item_field(NullS, NullS, field_info->field_name);
+  if (add_item_to_list(thd, field))
+    return 1;
+  field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
+  if (thd->lex->verbose)
+  {
+    field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
+    field_info= &schema_table->fields_info[3];
+    field= new Item_field(NullS, NullS, field_info->field_name);
+    if (add_item_to_list(thd, field))
+      return 1;
+    field->set_name(field_info->old_name, strlen(field_info->old_name),
+                    system_charset_info);
+  }
+  return 0;
+}
+
+
+int make_columns_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  ST_FIELD_INFO *field_info= &schema_table->fields_info[3];
+  int count= 2;
+  for ( ; field_info->field_name; field_info++)
+  {
+    count++;
+    if (field_info->old_name)
+    {
+      if (!thd->lex->verbose && (count == 12 ||count == 17 || count == 18))
+	continue;
+      Item_field *field= new Item_field(NullS, NullS, field_info->field_name);
+      if (field)
+      {
+        field->set_name(field_info->old_name,
+                        strlen(field_info->old_name),
+                        system_charset_info);
+        if (add_item_to_list(thd, field))
+          return 1;
+      }
+    }
+  }
+  return 0;
+}
+
+
+/*
+  Create information_schema table
+
+  SYNOPSIS
+  mysql_schema_table()
+    thd                thread handler
+    lex                pointer to LEX
+    table_list         pointer to table_list
+
+  RETURN
+    0	success
+    1   error
+*/
+
+int mysql_schema_table(THD *thd, LEX *lex, TABLE_LIST *table_list)
+{
+  TABLE *table;
+  DBUG_ENTER("mysql_schema_table");
+  if (!(table= table_list->schema_table->
+	create_table(thd, table_list->schema_table)))
+  {
+    DBUG_RETURN(1);
+  }
+  table->tmp_table= TMP_TABLE;
+  table->grant.privilege= SELECT_ACL;
+  table_list->real_name= table->real_name;
+  table_list->table= table;
+  table->next= thd->derived_tables;
+  thd->derived_tables= table;
+  table_list->select_lex->options |= OPTION_SCHEMA_TABLE;
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Generate select from information_schema table
+
+  SYNOPSIS
+    make_schema_select()
+    thd                  thread handler
+    sel                  pointer to SELECT_LEX
+    schema_table_idx     index of 'schema_tables' element
+
+  RETURN
+    0	success
+    1   error
+*/
+
+int make_schema_select(THD *thd, SELECT_LEX *sel,
+		       enum enum_schema_tables schema_table_idx)
+{
+  ST_SCHEMA_TABLE *schema_table= get_schema_table(schema_table_idx);
+  LEX_STRING db, table;
+  DBUG_ENTER("mysql_schema_select");
+  /* 
+     We have to make non const db_name & table_name
+     because of lower_case_table_names
+  */
+  make_lex_string(thd, &db, information_schema_name.str,
+                  information_schema_name.length, 0);
+  make_lex_string(thd, &table, schema_table->table_name,
+                  strlen(schema_table->table_name), 0);
+  if (!sel->item_list.elements &&          /* Handle old syntax */
+      schema_table->old_format(thd, schema_table) ||
+      !sel->add_table_to_list(thd, new Table_ident(thd, db, table, 0),
+                              0, 0, TL_READ, (List<String> *) 0,
+                              (List<String> *) 0))
+  {
+    DBUG_RETURN(1);
+  }
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Fill temporaty schema tables before SELECT
+
+  SYNOPSIS
+    get_schema_tables_result()
+    join  join which use schema tables
+
+  RETURN
+    0	success
+    1   error
+*/
+
+int get_schema_tables_result(JOIN *join)
+{
+  DBUG_ENTER("get_schema_tables_result");
+  JOIN_TAB *tmp_join_tab= join->join_tab+join->tables;
+  THD *thd= join->thd;
+  for (JOIN_TAB *tab= join->join_tab; tab < tmp_join_tab; tab++)
+  {  
+    if (!tab->table || !tab->table->pos_in_table_list)
+      break;
+    TABLE_LIST *table_list= tab->table->pos_in_table_list;
+    if (table_list->schema_table && !thd->only_prepare())
+    {
+      TABLE *old_derived_tables= thd->derived_tables;
+      thd->derived_tables= 0;
+      thd->lex->sql_command= SQLCOM_SHOW_FIELDS;
+      table_list->table->file->records= 0;
+      MYSQL_LOCK *sql_lock= thd->lock;
+      thd->lock=0;
+      if (table_list->schema_table->fill_table(thd, table_list,
+                                               tab->select_cond))
+      {
+        thd->derived_tables= old_derived_tables;
+        thd->lock= sql_lock;
+        DBUG_RETURN(-1);
+      }
+      thd->lock= sql_lock;
+      thd->lex->sql_command= SQLCOM_SELECT;
+      thd->derived_tables= old_derived_tables;
+    }
+  }
+  DBUG_RETURN(0);
+}
+
+
+ST_FIELD_INFO schema_fields_info[]=
+{
+  {"CATALOG_NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"SCHEMA_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Database"},
+  {"DEFAULT_CHARACTER_SET_NAME", 60, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO tables_fields_info[]=
+{
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Name"},
+  {"TABLE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ENGINE", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, "Engine"},
+  {"VERSION", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Version"},
+  {"ROW_FORMAT", 10, MYSQL_TYPE_STRING, 0, 1, 1, "Row_format"},
+  {"ROWS", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Rows"},
+  {"AVG_ROW_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Avg_row_length"},
+  {"DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Data_length"},
+  {"MAX_DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Max_data_length"},
+  {"INDEX_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Index_length"},
+  {"DATA_FREE", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Data_free"},
+  {"AUTO_INCREMENT", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Auto_increment"},
+  {"CREATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, 0, "Create_time"},
+  {"UPDATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, 0, "Update_time"},
+  {"CHECK_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, 0, "Check_time"},
+  {"COLLATION", 60, MYSQL_TYPE_STRING, 0, 1, 1, "Collation"},
+  {"CHECKSUM", 21 , MYSQL_TYPE_LONG, 0, 1, 0, "Checksum"},
+  {"CREATE_OPTIONS", 255, MYSQL_TYPE_STRING, 0, 1, 1, "Create_options"},
+  {"COMMENT", 80, MYSQL_TYPE_STRING, 0, 0, 1, "Comment"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO columns_fields_info[]=
+{
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Field"},
+  {"ORDINAL_POSITION", 21 , MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"DATA_TYPE", 40, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CHARACTER_MAXIMUM_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"CHARACTER_OCTET_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"NUMERIC_PRECISION", 21 , MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"NUMERIC_SCALE", 21 , MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"CHARACTER_SET_NAME", 40, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TYPE", 40, MYSQL_TYPE_STRING, 0, 0, 1, "Type"},
+  {"COLLATION_NAME", 40, MYSQL_TYPE_STRING, 0, 1, 1, "Collation"},
+  {"IS_NULLABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, "Null"},
+  {"KEY", 3, MYSQL_TYPE_STRING, 0, 0, 1, "Key"},
+  {"COLUMN_DEFAULT", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, "Default"},
+  {"EXTRA", 20, MYSQL_TYPE_STRING, 0, 0, 1, "Extra"},
+  {"PRIVILIGES", 80, MYSQL_TYPE_STRING, 0, 0, 1, "Privileges"},
+  {"COMMENT", 255, MYSQL_TYPE_STRING, 0, 0, 1, "Comment"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO charsets_fields_info[]=
+{
+  {"CHARACTER_SET_NAME", 30, MYSQL_TYPE_STRING, 0, 0, 1, "Charset"},
+  {"Description", 60, MYSQL_TYPE_STRING, 0, 0, 1, "Description"},
+  {"DEFAULT_COLLATE_NAME", 60, MYSQL_TYPE_STRING, 0, 0, 1, "Default collation"},
+  {"Maxlen", 3 ,MYSQL_TYPE_LONG, 0, 0, 0, "Maxlen"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO collation_fields_info[]=
+{
+  {"COLLATION_NAME", 30, MYSQL_TYPE_STRING, 0, 0, 1, "Collation"},
+  {"Charset", 30, MYSQL_TYPE_STRING, 0, 0, 1, "Charset"},
+  {"Id", 11, MYSQL_TYPE_LONG, 0, 0, 0, "Id"},
+  {"Default", 30 ,MYSQL_TYPE_STRING, 0, 0, 1, "Default"},
+  {"Compiled", 30 ,MYSQL_TYPE_STRING, 0, 0, 1, "Compiled"},
+  {"Sortlen", 3 ,MYSQL_TYPE_LONG, 0, 0, 0, "Sortlen"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO coll_charset_app_fields_info[]=
+{
+  {"COLLATION_NAME", 30, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CHARACTER_SET_NAME", 30, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO proc_fields_info[]=
+{
+  {"SPECIFIC_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ROUTINE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"ROUTINE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Db"},
+  {"ROUTINE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Name"},
+  {"ROUTINE_TYPE", 9, MYSQL_TYPE_STRING, 0, 0, 1, "Type"},
+  {"DEFINER", 77, MYSQL_TYPE_STRING, 0, 0, 1, "Definer"},
+  {"DTD_IDENTIFIER", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ROUTINE_BODY", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ROUTINE_DEFINITION", 65535, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"PARAMETER_STYLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_DETERMINISTIC", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"SQL_DATA_ACCESS", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"SQL_PATH", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"LAST_ALTERED", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0, "Modified"},
+  {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 1, "Created"},
+  {"SECURITY_TYPE", 7, MYSQL_TYPE_STRING, 0, 0, 1, "Security_type"},
+  {"SQL_MODE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ROUTINE_COMMENT", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Comment"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO stat_fields_info[]=
+{
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Table"},
+  {"NON_UNIQUE", 1, MYSQL_TYPE_LONG, 0, 0, 0, "Non_unique"},
+  {"INDEX_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"INDEX_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Key_name"},
+  {"SEQ_IN_INDEX", 2, MYSQL_TYPE_LONG, 0, 0, 0, "Seq_in_index"},
+  {"COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Column_name"},
+  {"COLLATION", 1, MYSQL_TYPE_STRING, 0, 1, 1, "Collation"},
+  {"CARDINALITY", 21, MYSQL_TYPE_LONG, 0, 1, 0, "Cardinality"},
+  {"SUB_PART", 3, MYSQL_TYPE_LONG, 0, 1, 0, "Sub_part"},
+  {"PACKED", 10, MYSQL_TYPE_STRING, 0, 1, 1, "Packed"},
+  {"NULLABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, "Null"},
+  {"INDEX_TYPE", 16, MYSQL_TYPE_STRING, 0, 0, 1, "Index_type"},
+  {"COMMENT", 16, MYSQL_TYPE_STRING, 0, 1, 1, "Comment"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO view_fields_info[]=
+{
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"VIEW_DEFINITION", 65535, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CHECK_OPTION", 4, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_UPDATABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO user_privileges_fields_info[]=
+{
+  {"GRANTEE", 81, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"PRIVILEGE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_GRANTABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO schema_privileges_fields_info[]=
+{
+  {"GRANTEE", 81, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"PRIVILEGE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_GRANTABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO table_privileges_fields_info[]=
+{
+  {"GRANTEE", 81, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"PRIVILEGE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_GRANTABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO column_privileges_fields_info[]=
+{
+  {"GRANTEE", 81, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"PRIVILEGE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"IS_GRANTABLE", 3, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO table_constraints_fields_info[]=
+{
+  {"CONSTRAINT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"CONSTRAINT_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CONSTRAINT_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CONSTRAINT_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CONSTRAINT_METHOD", 20, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO key_column_usage_fields_info[]=
+{
+  {"CONSTRAINT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"CONSTRAINT_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"CONSTRAINT_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"ORDINAL_POSITION", 10 ,MYSQL_TYPE_LONG, 0, 0, 0, 0},
+  {"REFERENCED_TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"REFERENCED_TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"REFERENCED_COLUMN_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+ST_FIELD_INFO table_names_fields_info[]=
+{
+  {"TABLE_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 1, 0},
+  {"TABLE_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, 0},
+  {"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Tables_in_"},
+  {"TABLE_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 1, "Table_type"},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0}
+};
+
+
+/*
+  Description of ST_FIELD_INFO in table.h
+*/
+
+ST_SCHEMA_TABLE schema_tables[]=
+{
+  {"SCHEMATA", schema_fields_info, create_schema_table,
+   fill_schema_shemata, make_schemata_old_format, 0, 1, -1},
+  {"TABLES", tables_fields_info, create_schema_table, 
+   get_all_tables, make_old_format, get_schema_tables_record, 1, 2},
+  {"COLUMNS", columns_fields_info, create_schema_table, 
+   get_all_tables, make_columns_old_format, get_schema_column_record, 1, 2},
+  {"CHARACTER_SETS", charsets_fields_info, create_schema_table, 
+   fill_schema_charsets, make_old_format, 0, -1, -1},
+  {"COLLATIONS", collation_fields_info, create_schema_table, 
+   fill_schema_collation, make_old_format, 0, -1, -1},
+  {"COLLATION_CHARACTER_SET_APPLICABILITY", coll_charset_app_fields_info,
+   create_schema_table, fill_schema_coll_charset_app, 0, 0, -1, -1},
+  {"ROUTINES", proc_fields_info, create_schema_table, 
+    fill_schema_proc, make_old_format, 0, -1, -1},
+  {"STATISTICS", stat_fields_info, create_schema_table, 
+    get_all_tables, make_old_format, get_schema_stat_record, 1, 2},
+  {"VIEWS", view_fields_info, create_schema_table, 
+    get_all_tables, 0, get_schema_views_record, 1, 2},
+  {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table, 
+    fill_schema_user_privileges, 0, 0, -1, -1},
+  {"SCHEMA_PRIVILEGES", schema_privileges_fields_info, create_schema_table,
+    fill_schema_schema_privileges, 0, 0, -1, -1},
+  {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
+    fill_schema_table_privileges, 0, 0, -1, -1},
+  {"COLUMN_PRIVILEGES", column_privileges_fields_info, create_schema_table,
+    fill_schema_column_privileges, 0, 0, -1, -1},
+  {"TABLE_CONSTRAINTS", table_constraints_fields_info, create_schema_table,
+    get_all_tables, 0, get_schema_constarints_record, 3, 4},
+  {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
+    get_all_tables, 0, get_schema_key_column_usage_record, 3, 4},
+  {"TABLE_NAMES", table_names_fields_info, create_schema_table,
+   get_all_tables, make_table_names_old_format, 0, 1, 2},
+  {0, 0, 0, 0, 0, 0, 0, 0}
+};
+
+
 #ifdef __GNUC__
 template class List_iterator_fast<char>;
 template class List<char>;
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 760ada9cdbd..941f08094b5 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -188,7 +188,8 @@ int mysql_create_view(THD *thd,
   for (tbl= tables; tbl; tbl= tbl->next_global)
   {
     /* is this table temporary and is not view? */
-    if (tbl->table->tmp_table != NO_TMP_TABLE && !tbl->view)
+    if (tbl->table->tmp_table != NO_TMP_TABLE && !tbl->view &&
+        !tbl->schema_table)
     {
       my_error(ER_VIEW_SELECT_TMPTABLE, MYF(0), tbl->alias);
       res= -1;
@@ -494,7 +495,7 @@ static int mysql_register_view(THD *thd, TABLE_LIST *view,
          tbl;
          tbl= tbl->next_local)
     {
-      if (tbl->view && !tbl->updatable_view)
+      if ((tbl->view && !tbl->updatable_view) || tbl->schema_table)
       {
         view->updatable_view= 0;
         break;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index fe5ce7640ea..2f6cb53fc04 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -663,7 +663,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 	opt_table_alias
 
 %type <table>
-	table_ident table_ident_nodb references
+	table_ident table_ident_nodb references from_table_ident
 
 %type <simple_string>
 	remember_name remember_end opt_ident opt_db text_or_password
@@ -5904,21 +5904,32 @@ show:	SHOW
 	;
 
 show_param:
-	DATABASES wild
-	  { Lex->sql_command= SQLCOM_SHOW_DATABASES; }
-	| opt_full TABLES opt_db wild
-	  {
-	    LEX *lex= Lex;
-	    lex->sql_command= SQLCOM_SHOW_TABLES;
-	    lex->select_lex.db= $3;
-	   }
-	| TABLE_SYM STATUS_SYM opt_db wild
-	  {
-	    LEX *lex= Lex;
-	    lex->sql_command= SQLCOM_SHOW_TABLES;
-	    lex->describe= DESCRIBE_EXTENDED;
-	    lex->select_lex.db= $3;
-	  }
+         DATABASES ext_select_item_list wild_and_where
+         {
+           LEX *lex= Lex;
+           lex->sql_command= SQLCOM_SELECT;
+           lex->orig_sql_command= SQLCOM_SHOW_DATABASES;
+           if (prepare_schema_table(YYTHD, lex, 0, SCH_SCHEMATA))
+             YYABORT;
+         }
+         | opt_full TABLES ext_select_item_list opt_db wild_and_where
+           {
+             LEX *lex= Lex;
+             lex->sql_command= SQLCOM_SELECT;
+             lex->orig_sql_command= SQLCOM_SHOW_TABLES;
+             lex->select_lex.db= $4;
+             if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))
+               YYABORT;
+           }
+         | TABLE_SYM STATUS_SYM ext_select_item_list opt_db wild_and_where
+           {
+             LEX *lex= Lex;
+             lex->sql_command= SQLCOM_SELECT;
+             lex->orig_sql_command= SQLCOM_SHOW_TABLE_STATUS;
+             lex->select_lex.db= $4;
+             if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLES))
+               YYABORT;
+           }
 	| OPEN_SYM TABLES opt_db wild
 	  {
 	    LEX *lex= Lex;
@@ -5928,12 +5939,14 @@ show_param:
 	| ENGINE_SYM storage_engines 
 	  { Lex->create_info.db_type= $2; }
 	  show_engine_param
-	| opt_full COLUMNS from_or_in table_ident opt_db wild
+	| opt_full COLUMNS ext_select_item_list from_table_ident opt_db wild_and_where
 	  {
-	    Lex->sql_command= SQLCOM_SHOW_FIELDS;
+ 	    LEX *lex= Lex;
+	    lex->sql_command= SQLCOM_SELECT;
+	    lex->orig_sql_command= SQLCOM_SHOW_FIELDS;
 	    if ($5)
 	      $4->change_db($5);
-	    if (!Select->add_table_to_list(YYTHD, $4, NULL, 0))
+	    if (prepare_schema_table(YYTHD, lex, $4, SCH_COLUMNS))
 	      YYABORT;
 	  }
         | NEW_SYM MASTER_SYM FOR_SYM SLAVE WITH MASTER_LOG_FILE_SYM EQ
@@ -5959,13 +5972,15 @@ show_param:
 	    LEX *lex= Lex;
 	    lex->sql_command= SQLCOM_SHOW_BINLOG_EVENTS;
           } opt_limit_clause_init
-	| keys_or_index from_or_in table_ident opt_db
-	  {
-	    Lex->sql_command= SQLCOM_SHOW_KEYS;
+        | keys_or_index ext_select_item_list from_table_ident opt_db where_clause
+          {
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SELECT;
+            lex->orig_sql_command= SQLCOM_SHOW_KEYS;
 	    if ($4)
 	      $3->change_db($4);
-	    if (!Select->add_table_to_list(YYTHD, $3, NULL, 0))
-	      YYABORT;
+            if (prepare_schema_table(YYTHD, lex, $3, SCH_STATISTICS))
+              YYABORT;
 	  }
 	| COLUMN_SYM TYPES_SYM
 	  {
@@ -6012,10 +6027,22 @@ show_param:
 	    thd->lex->sql_command= SQLCOM_SHOW_VARIABLES;
 	    thd->lex->option_type= (enum_var_type) $1;
 	  }
-	| charset wild
-	  { Lex->sql_command= SQLCOM_SHOW_CHARSETS; }
-	| COLLATION_SYM wild
-	  { Lex->sql_command= SQLCOM_SHOW_COLLATIONS; }
+        | charset ext_select_item_list wild_and_where
+          {
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SELECT;
+            lex->orig_sql_command= SQLCOM_SHOW_CHARSETS;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_CHARSETS))
+              YYABORT;
+          }
+        | COLLATION_SYM ext_select_item_list wild_and_where
+          {
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SELECT;
+            lex->orig_sql_command= SQLCOM_SHOW_COLLATIONS;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_COLLATIONS))
+              YYABORT;
+          }
 	| BERKELEY_DB_SYM LOGS_SYM
 	  { Lex->sql_command= SQLCOM_SHOW_LOGS; WARN_DEPRECATED("SHOW BDB LOGS", "SHOW ENGINE BDB LOGS"); }
 	| LOGS_SYM
@@ -6094,13 +6121,21 @@ show_param:
 	    lex->sql_command = SQLCOM_SHOW_CREATE_FUNC;
 	    lex->spname= $3;
 	  }
-	| PROCEDURE STATUS_SYM wild
+	| PROCEDURE STATUS_SYM ext_select_item_list wild_and_where
 	  {
-	    Lex->sql_command = SQLCOM_SHOW_STATUS_PROC;
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SELECT;
+            lex->orig_sql_command= SQLCOM_SHOW_STATUS_PROC;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
+              YYABORT;
 	  }
-	| FUNCTION_SYM STATUS_SYM wild
+	| FUNCTION_SYM STATUS_SYM ext_select_item_list wild_and_where
 	  {
-	    Lex->sql_command = SQLCOM_SHOW_STATUS_FUNC;
+            LEX *lex= Lex;
+            lex->sql_command= SQLCOM_SELECT;
+            lex->orig_sql_command= SQLCOM_SHOW_STATUS_FUNC;
+            if (prepare_schema_table(YYTHD, lex, 0, SCH_PROCEDURES))
+              YYABORT;
 	  };
 
 show_engine_param:
@@ -6161,16 +6196,49 @@ binlog_from:
 	/* empty */ { Lex->mi.pos = 4; /* skip magic number */ }
         | FROM ulonglong_num { Lex->mi.pos = $2; };
 
+from_table_ident:
+      /* empty */       { $$= 0; }
+      | from_or_in table_ident  { $$= $2; }
+      ;
+
+wild_and_where:
+      /* empty */
+      | LIKE TEXT_STRING_sys
+	{ Lex->wild=  new (YYTHD->mem_root) String($2.str, $2.length,
+                                                   system_charset_info); }
+      | WHERE expr
+        {
+          Select->where= $2;
+          if ($2)
+            $2->top_level_item();
+        }
+      ;
+
+ext_select_item_list:
+      {
+        LEX *lex=Lex;
+        SELECT_LEX *sel= lex->current_select;
+        lex->lock_option= TL_READ;
+        mysql_init_select(lex);
+        lex->current_select->parsing_place= SELECT_LIST;
+      }
+      /* empty */
+      | select_item_list {};
+
 
 /* A Oracle compatible synonym for show */
 describe:
 	describe_command table_ident
 	{
-	  LEX *lex=Lex;
-	  lex->wild=0;
-	  lex->verbose=0;
-	  lex->sql_command=SQLCOM_SHOW_FIELDS;
-	  if (!Select->add_table_to_list(lex->thd, $2, NULL,0))
+          LEX *lex= Lex;
+          lex->lock_option= TL_READ;
+          mysql_init_select(lex);
+          lex->current_select->parsing_place= SELECT_LIST;
+          lex->sql_command= SQLCOM_SELECT;
+          lex->orig_sql_command= SQLCOM_SHOW_FIELDS;
+          lex->select_lex.db= 0;
+          lex->verbose= 0;
+          if (prepare_schema_table(YYTHD, lex, $2, SCH_COLUMNS))
 	    YYABORT;
 	}
 	opt_describe_column {}
diff --git a/sql/table.cc b/sql/table.cc
index 0e8045abacf..68293335155 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1520,6 +1520,7 @@ void st_table_list::set_ancestor()
   if (ancestor->ancestor)
     ancestor->set_ancestor();
   table= ancestor->table;
+  schema_table= ancestor->schema_table;
   ancestor->table->grant= grant;
 }
 
diff --git a/sql/table.h b/sql/table.h
index f95be1fcccb..86dd30dff68 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -204,6 +204,58 @@ struct st_table {
 };
 
 
+typedef struct st_foreign_key_info
+{
+  LEX_STRING *forein_id;
+  LEX_STRING *referenced_db;
+  LEX_STRING *referenced_table;
+  LEX_STRING *constraint_method;
+  List<LEX_STRING> foreign_fields;
+  List<LEX_STRING> referenced_fields;
+} FOREIGN_KEY_INFO;
+
+
+enum enum_schema_tables
+{
+  SCH_SCHEMATA= 0, SCH_TABLES, SCH_COLUMNS, SCH_CHARSETS, SCH_COLLATIONS,
+  SCH_COLLATION_CHARACTER_SET_APPLICABILITY, SCH_PROCEDURES, SCH_STATISTICS,
+  SCH_VIEWS, SCH_USER_PRIVILEGES, SCH_SCHEMA_PRIVILEGES, SCH_TABLE_PRIVILEGES,
+  SCH_COLUMN_PRIVILEGES, SCH_TABLE_CONSTRAINTS, SCH_KEY_COLUMN_USAGE,
+  SCH_TABLE_NAMES
+};
+
+
+typedef struct st_field_info
+{
+  const char* field_name;
+  uint field_length;
+  enum enum_field_types field_type;
+  int value;
+  bool maybe_null;
+  bool utf8;
+  const char* old_name;
+} ST_FIELD_INFO;
+
+struct st_table_list;
+typedef class Item COND;
+
+typedef struct st_schema_table
+{
+  const char* table_name;
+  ST_FIELD_INFO *fields_info;
+  /* Create information_schema table */
+  TABLE *(*create_table)  (THD *thd, struct st_schema_table *schema_table);
+  /* Fill table with data */
+  int (*fill_table) (THD *thd, struct st_table_list *tables, COND *cond);
+  /* Handle fileds for old SHOW */
+  int (*old_format) (THD *thd, struct st_schema_table *schema_table);
+  int (*process_table) (THD *thd, struct st_table_list *tables,
+                        TABLE *table, int res, const char *base_name,
+                        const char *file_name);
+  int idx_field1, idx_field2; 
+} ST_SCHEMA_TABLE;
+
+
 #define JOIN_TYPE_LEFT	1
 #define JOIN_TYPE_RIGHT	2
 
@@ -252,6 +304,8 @@ typedef struct st_table_list
   */
   st_table_list	*correspondent_table;
   st_select_lex_unit *derived;		/* SELECT_LEX_UNIT of derived table */
+  ST_SCHEMA_TABLE *schema_table;        /* Information_schema table */
+  st_select_lex	*schema_select_lex;
   /* link to select_lex where this table was used */
   st_select_lex	*select_lex;
   st_lex	*view;			/* link on VIEW lex for merging */
diff --git a/tests/client_test.c b/tests/client_test.c
index 39969120608..034d846017a 100644
--- a/tests/client_test.c
+++ b/tests/client_test.c
@@ -655,10 +655,12 @@ static void verify_prepare_field(MYSQL_RES *result,
     fprintf(stdout, "\n    org_name :`%s`\t(expected: `%s`)",
             field->org_name, org_name);
     fprintf(stdout, "\n    type     :`%d`\t(expected: `%d`)", field->type, type);
-    fprintf(stdout, "\n    table    :`%s`\t(expected: `%s`)",
-            field->table, table);
-    fprintf(stdout, "\n    org_table:`%s`\t(expected: `%s`)",
-            field->org_table, org_table);
+    if (table)
+      fprintf(stdout, "\n    table    :`%s`\t(expected: `%s`)",
+              field->table, table);
+    if (org_table)	      
+      fprintf(stdout, "\n    org_table:`%s`\t(expected: `%s`)",
+              field->org_table, org_table);
     fprintf(stdout, "\n    database :`%s`\t(expected: `%s`)", field->db, db);
     fprintf(stdout, "\n    length   :`%ld`\t(expected: `%ld`)",
             field->length, length);
@@ -671,8 +673,10 @@ static void verify_prepare_field(MYSQL_RES *result,
   DIE_UNLESS(strcmp(field->name, name) == 0);
   DIE_UNLESS(strcmp(field->org_name, org_name) == 0);
   DIE_UNLESS(field->type == type);
-  DIE_UNLESS(strcmp(field->table, table) == 0);
-  DIE_UNLESS(strcmp(field->org_table, org_table) == 0);
+  if (table)
+    DIE_UNLESS(strcmp(field->table, table) == 0);
+  if (org_table)
+    DIE_UNLESS(strcmp(field->org_table, org_table) == 0);
   DIE_UNLESS(strcmp(field->db, db) == 0);
   DIE_UNLESS(field->length == length);
   if (def)
@@ -7256,23 +7260,23 @@ static void test_explain_bug()
             mysql_num_fields(result));
   DIE_UNLESS(6 == mysql_num_fields(result));
 
-  verify_prepare_field(result, 0, "Field", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", NAME_LEN, 0);
+  verify_prepare_field(result, 0, "Field", "COLUMN_NAME",
+                       MYSQL_TYPE_STRING, 0, 0, "", NAME_LEN, 0);
 
-  verify_prepare_field(result, 1, "Type", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", 40, 0);
+  verify_prepare_field(result, 1, "Type", "TYPE",
+                       MYSQL_TYPE_STRING, 0, 0, "", 40, 0);
 
-  verify_prepare_field(result, 2, "Null", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", 1, 0);
+  verify_prepare_field(result, 2, "Null", "IS_NULLABLE",
+                       MYSQL_TYPE_STRING, 0, 0, "", 3, 0);
 
-  verify_prepare_field(result, 3, "Key", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", 3, 0);
+  verify_prepare_field(result, 3, "Key", "KEY",
+                       MYSQL_TYPE_STRING, 0, 0, "", 3, 0);
 
-  verify_prepare_field(result, 4, "Default", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", NAME_LEN, 0);
+  verify_prepare_field(result, 4, "Default", "COLUMN_DEFAULT",
+                       MYSQL_TYPE_STRING, 0, 0, "", NAME_LEN, 0);
 
-  verify_prepare_field(result, 5, "Extra", "", MYSQL_TYPE_VAR_STRING,
-                       "", "", "", 20, 0);
+  verify_prepare_field(result, 5, "Extra", "EXTRA",
+                       MYSQL_TYPE_STRING, 0, 0, "", 20, 0);
 
   mysql_free_result(result);
   mysql_stmt_close(stmt);