Sql:mysql 6.7 table, view, stored procedure structure query

#数据库MySQL 6.7use Sakila; #查询表名show tables; #SELECT table_name,table_rows from Information_schema. TABLES where table_schema= ' Sakila '; select column_name from Information_schema.columns WHERE table_schema= ' Sakila ' and Table_name= ' actor '; #表结构 field name, type, length select * from Information_schema.columns where table_schema= ' Sakila ' and Table_name= ' Actor '; # gettablesselect table_name, ' OWNER, create_time from INFORMATION_SCHEMA. TABLES WHERE table_schema = ' Sakila ' and table_type = ' BASE TABLE ' ORDER by 1; #GetTableColumnsSELECT column_name, Data_typ E, Character_octet_length, Numeric_precision, Numeric_scale, Case is_nullable if ' NO ' then 0 ELSE 1 END is_nullable, COL Umn_type from INFORMATION_SCHEMA. COLUMNS WHERE table_schema = ' Sakila ' and table_name = ' address ' ORDER by ordinal_position; #GetViewsSELECT table_name, ' OWNER, Create_time from INFORMATION_SCHEMA. TABLES WHERE table_schema = ' Sakila ' and table_type = ' VIEW ' ORDER by 1; #GetViewColumnsSELECT column_name, Data_type, CHAR Acter_octet_length, NUmeric_precision, Numeric_scale, Case is_nullable if ' NO ' then 0 ELSE 1 END is_nullable, column_type from INFORMATION_SC HEMA. COLUMNS WHERE table_schema = ' Sakila ' and table_name = ' actor_info ' ORDER by ordinal_position; #GetTablePrimaryKeySELECT T1 . Constraint_name, T1. column_name from INFORMATION_SCHEMA. key_column_usage T1 INNER JOIN information_schema. Table_constraints T2 on T2. Table_schema = t1. Table_schema and T2. table_name = t1. TABLE_NAME and T2. constraint_name = t1. Constraint_name WHERE t1. Table_schema = ' Sakila ' and T1. table_name = ' address ' and T2. Constraint_type = ' PRIMARY KEY ' ORDER by T1. ordinal_position; #GetTableIndexesSELECT index_name, COUNT (*) as Column_count, MAX (non_unique) Non_unique, Case index_ NAME when the ' PRIMARY ' then 1 ELSE 0 END is_primaryfrom information_schema. Statisticswhere table_schema = ' Sakila ' and table_name = ' customer ' GROUP by Index_nameorder by Index_name; SELECT index_name, Column_namefrom information_schema. Statisticswhere table_schema = ' Sakila ' AND table_name = ' Customer ' ORDER by Index_name, Seq_in_index; #GetMyTableKeysSELECT constraint_name from INF Ormation_schema. table_constraints T1 WHERE T1. Table_schema = ' Sakila ' and T1. table_name = ' customer ' and constraint_type = ' FOREIGN KEY '; SELECT t1. Constraint_name, T1. column_name, T1. Position_in_unique_constraint,t1. Referenced_table_name, Referenced_column_name from INFORMATION_SCHEMA. key_column_usage T1 INNER JOIN information_schema. Table_constraints T2 on T2. Table_schema = t1. Table_schema and T2. table_name = t1. TABLE_NAME and T2. constraint_name = t1. Constraint_name WHERE t1. Table_schema = ' Sakila ' and T1. table_name = ' customer ' and T2. Constraint_type = ' FOREIGN KEY ' ORDER by T1. Constraint_name, T1. Position_in_unique_constraint, #GetOthersTableKeysSELECT DISTINCT constraint_name from INFORMation_schema. key_column_usage T1 WHERE T1. Table_schema = ' Sakila ' and T1.                 Referenced_table_name = ' Customer '; SELECT t1. Constraint_name, T1. TABLE_NAME, T1. column_name, T1. Position_in_unique_constraint, T1. Referenced_table_name, Referenced_column_name from INFORMATION_SCHEMA. key_column_usage T1 INNER JOIN information_schema. Table_constraints T2 on T2. Table_schema = t1. Table_schema and T2. table_name = t1. TABLE_NAME and T2. constraint_name = t1. Constraint_name WHERE t1. Table_schema = ' Sakila ' and T1. Referenced_table_name = ' customer ' and T2. Constraint_type = ' FOREIGN KEY ' ORDER by T1. Constraint_name, T1. Position_in_unique_constraint; #GetViewTextSELECT view_definition from Information_schema.  views WHERE Table_schema = ' Sakila ' and table_name = ' actor_info '; # getcommandsselect Routine_name, "OWNER, CREATED from Information_scheMA. ROUTINES WHERE routine_schema = ' Sakila ' and routine_type = ' PROCEDURE ' ORDER by 1; #GetCommandParameters #getcommandtextse Lect routine_definition from INFORMATION_SCHEMA. ROUTINES WHERE routine_schema = ' Sakila ' and routine_name = ' Film_in_stock ' #存储过程

　　

Sql:mysql 6.7 table, view, stored procedure structure query