1use INFORMATION_SCHEMA; 2show tables; 3Tables_in_information_schema 4ALL_PLUGINS 5APPLICABLE_ROLES 6CHARACTER_SETS 7CHECK_CONSTRAINTS 8CLIENT_STATISTICS 9COLLATIONS 10COLLATION_CHARACTER_SET_APPLICABILITY 11COLUMNS 12COLUMN_PRIVILEGES 13ENABLED_ROLES 14ENGINES 15EVENTS 16FILES 17GEOMETRY_COLUMNS 18GLOBAL_STATUS 19GLOBAL_VARIABLES 20INDEX_STATISTICS 21INNODB_BUFFER_PAGE 22INNODB_BUFFER_PAGE_LRU 23INNODB_BUFFER_POOL_STATS 24INNODB_CMP 25INNODB_CMPMEM 26INNODB_CMPMEM_RESET 27INNODB_CMP_PER_INDEX 28INNODB_CMP_RESET 29INNODB_LOCKS 30INNODB_LOCK_WAITS 31INNODB_METRICS 32INNODB_MUTEXES 33INNODB_SYS_COLUMNS 34INNODB_SYS_FIELDS 35INNODB_SYS_FOREIGN 36INNODB_SYS_FOREIGN_COLS 37INNODB_SYS_INDEXES 38INNODB_SYS_TABLES 39INNODB_SYS_TABLESTATS 40INNODB_SYS_VIRTUAL 41INNODB_TABLESPACES_ENCRYPTION 42INNODB_TABLESPACES_SCRUBBING 43INNODB_TRX 44KEYWORDS 45KEY_CACHES 46KEY_COLUMN_USAGE 47PARAMETERS 48PARTITIONS 49PLUGINS 50PROCESSLIST 51PROFILING 52REFERENTIAL_CONSTRAINTS 53ROUTINES 54SCHEMATA 55SCHEMA_PRIVILEGES 56SESSION_STATUS 57SESSION_VARIABLES 58SPATIAL_REF_SYS 59SQL_FUNCTIONS 60STATISTICS 61SYSTEM_VARIABLES 62TABLES 63TABLESPACES 64TABLE_CONSTRAINTS 65TABLE_PRIVILEGES 66TABLE_STATISTICS 67TRIGGERS 68USER_PRIVILEGES 69USER_STATISTICS 70VIEWS 71SELECT t.table_name, c1.column_name 72FROM information_schema.tables t 73INNER JOIN 74information_schema.columns c1 75ON t.table_schema = c1.table_schema AND 76t.table_name = c1.table_name 77WHERE t.table_schema = 'information_schema' AND 78c1.ordinal_position = 79( SELECT COALESCE(MIN(c2.ordinal_position),1) 80FROM information_schema.columns c2 81WHERE c2.table_schema = t.table_schema AND 82c2.table_name = t.table_name AND 83c2.column_name LIKE '%SCHEMA%' 84 ) order by t.table_name; 85table_name column_name 86ALL_PLUGINS PLUGIN_NAME 87APPLICABLE_ROLES GRANTEE 88CHARACTER_SETS CHARACTER_SET_NAME 89CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 90CLIENT_STATISTICS CLIENT 91COLLATIONS COLLATION_NAME 92COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME 93COLUMNS TABLE_SCHEMA 94COLUMN_PRIVILEGES TABLE_SCHEMA 95ENABLED_ROLES ROLE_NAME 96ENGINES ENGINE 97EVENTS EVENT_SCHEMA 98FILES TABLE_SCHEMA 99GEOMETRY_COLUMNS F_TABLE_SCHEMA 100GLOBAL_STATUS VARIABLE_NAME 101GLOBAL_VARIABLES VARIABLE_NAME 102INDEX_STATISTICS TABLE_SCHEMA 103INNODB_BUFFER_PAGE POOL_ID 104INNODB_BUFFER_PAGE_LRU POOL_ID 105INNODB_BUFFER_POOL_STATS POOL_ID 106INNODB_CMP page_size 107INNODB_CMPMEM page_size 108INNODB_CMPMEM_RESET page_size 109INNODB_CMP_PER_INDEX database_name 110INNODB_CMP_RESET page_size 111INNODB_LOCKS lock_id 112INNODB_LOCK_WAITS requesting_trx_id 113INNODB_METRICS NAME 114INNODB_MUTEXES NAME 115INNODB_SYS_COLUMNS TABLE_ID 116INNODB_SYS_FIELDS INDEX_ID 117INNODB_SYS_FOREIGN ID 118INNODB_SYS_FOREIGN_COLS ID 119INNODB_SYS_INDEXES INDEX_ID 120INNODB_SYS_TABLES TABLE_ID 121INNODB_SYS_TABLESTATS TABLE_ID 122INNODB_SYS_VIRTUAL TABLE_ID 123INNODB_TABLESPACES_ENCRYPTION SPACE 124INNODB_TABLESPACES_SCRUBBING SPACE 125INNODB_TRX trx_id 126KEYWORDS WORD 127KEY_CACHES KEY_CACHE_NAME 128KEY_COLUMN_USAGE CONSTRAINT_SCHEMA 129PARAMETERS SPECIFIC_SCHEMA 130PARTITIONS TABLE_SCHEMA 131PLUGINS PLUGIN_NAME 132PROCESSLIST ID 133PROFILING QUERY_ID 134REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA 135ROUTINES ROUTINE_SCHEMA 136SCHEMATA SCHEMA_NAME 137SCHEMA_PRIVILEGES TABLE_SCHEMA 138SESSION_STATUS VARIABLE_NAME 139SESSION_VARIABLES VARIABLE_NAME 140SPATIAL_REF_SYS SRID 141SQL_FUNCTIONS FUNCTION 142STATISTICS TABLE_SCHEMA 143SYSTEM_VARIABLES VARIABLE_NAME 144TABLES TABLE_SCHEMA 145TABLESPACES TABLESPACE_NAME 146TABLE_CONSTRAINTS CONSTRAINT_SCHEMA 147TABLE_PRIVILEGES TABLE_SCHEMA 148TABLE_STATISTICS TABLE_SCHEMA 149TRIGGERS TRIGGER_SCHEMA 150USER_PRIVILEGES GRANTEE 151USER_STATISTICS USER 152VIEWS TABLE_SCHEMA 153SELECT t.table_name, c1.column_name 154FROM information_schema.tables t 155INNER JOIN 156information_schema.columns c1 157ON t.table_schema = c1.table_schema AND 158t.table_name = c1.table_name 159WHERE t.table_schema = 'information_schema' AND 160c1.ordinal_position = 161( SELECT COALESCE(MIN(c2.ordinal_position),1) 162FROM information_schema.columns c2 163WHERE c2.table_schema = 'information_schema' AND 164c2.table_name = t.table_name AND 165c2.column_name LIKE '%SCHEMA%' 166 ) order by t.table_name; 167table_name column_name 168ALL_PLUGINS PLUGIN_NAME 169APPLICABLE_ROLES GRANTEE 170CHARACTER_SETS CHARACTER_SET_NAME 171CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 172CLIENT_STATISTICS CLIENT 173COLLATIONS COLLATION_NAME 174COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME 175COLUMNS TABLE_SCHEMA 176COLUMN_PRIVILEGES TABLE_SCHEMA 177ENABLED_ROLES ROLE_NAME 178ENGINES ENGINE 179EVENTS EVENT_SCHEMA 180FILES TABLE_SCHEMA 181GEOMETRY_COLUMNS F_TABLE_SCHEMA 182GLOBAL_STATUS VARIABLE_NAME 183GLOBAL_VARIABLES VARIABLE_NAME 184INDEX_STATISTICS TABLE_SCHEMA 185INNODB_BUFFER_PAGE POOL_ID 186INNODB_BUFFER_PAGE_LRU POOL_ID 187INNODB_BUFFER_POOL_STATS POOL_ID 188INNODB_CMP page_size 189INNODB_CMPMEM page_size 190INNODB_CMPMEM_RESET page_size 191INNODB_CMP_PER_INDEX database_name 192INNODB_CMP_RESET page_size 193INNODB_LOCKS lock_id 194INNODB_LOCK_WAITS requesting_trx_id 195INNODB_METRICS NAME 196INNODB_MUTEXES NAME 197INNODB_SYS_COLUMNS TABLE_ID 198INNODB_SYS_FIELDS INDEX_ID 199INNODB_SYS_FOREIGN ID 200INNODB_SYS_FOREIGN_COLS ID 201INNODB_SYS_INDEXES INDEX_ID 202INNODB_SYS_TABLES TABLE_ID 203INNODB_SYS_TABLESTATS TABLE_ID 204INNODB_SYS_VIRTUAL TABLE_ID 205INNODB_TABLESPACES_ENCRYPTION SPACE 206INNODB_TABLESPACES_SCRUBBING SPACE 207INNODB_TRX trx_id 208KEYWORDS WORD 209KEY_CACHES KEY_CACHE_NAME 210KEY_COLUMN_USAGE CONSTRAINT_SCHEMA 211PARAMETERS SPECIFIC_SCHEMA 212PARTITIONS TABLE_SCHEMA 213PLUGINS PLUGIN_NAME 214PROCESSLIST ID 215PROFILING QUERY_ID 216REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA 217ROUTINES ROUTINE_SCHEMA 218SCHEMATA SCHEMA_NAME 219SCHEMA_PRIVILEGES TABLE_SCHEMA 220SESSION_STATUS VARIABLE_NAME 221SESSION_VARIABLES VARIABLE_NAME 222SPATIAL_REF_SYS SRID 223SQL_FUNCTIONS FUNCTION 224STATISTICS TABLE_SCHEMA 225SYSTEM_VARIABLES VARIABLE_NAME 226TABLES TABLE_SCHEMA 227TABLESPACES TABLESPACE_NAME 228TABLE_CONSTRAINTS CONSTRAINT_SCHEMA 229TABLE_PRIVILEGES TABLE_SCHEMA 230TABLE_STATISTICS TABLE_SCHEMA 231TRIGGERS TRIGGER_SCHEMA 232USER_PRIVILEGES GRANTEE 233USER_STATISTICS USER 234VIEWS TABLE_SCHEMA 235select 1 as "must be 1" from information_schema.tables where "ACCOUNTS"= 236(select cast(table_name as char) from information_schema.tables 237order by table_name limit 1) limit 1; 238must be 1 2391 240select t.table_name, group_concat(t.table_schema, '.', t.table_name), 241count(*) as num1 242from information_schema.tables t 243inner join information_schema.columns c1 244on t.table_schema = c1.table_schema AND t.table_name = c1.table_name 245where t.table_schema = 'information_schema' and 246c1.ordinal_position = 247(select isnull(c2.column_type) - 248isnull(group_concat(c2.table_schema, '.', c2.table_name)) + 249count(*) as num 250from information_schema.columns c2 where 251c2.table_schema='information_schema' and 252(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)') 253group by c2.column_type order by num limit 1) 254group by t.table_name order by num1, t.table_name; 255table_name group_concat(t.table_schema, '.', t.table_name) num1 256ALL_PLUGINS information_schema.ALL_PLUGINS 1 257APPLICABLE_ROLES information_schema.APPLICABLE_ROLES 1 258CHARACTER_SETS information_schema.CHARACTER_SETS 1 259CHECK_CONSTRAINTS information_schema.CHECK_CONSTRAINTS 1 260CLIENT_STATISTICS information_schema.CLIENT_STATISTICS 1 261COLLATIONS information_schema.COLLATIONS 1 262COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1 263COLUMNS information_schema.COLUMNS 1 264COLUMN_PRIVILEGES information_schema.COLUMN_PRIVILEGES 1 265ENGINES information_schema.ENGINES 1 266EVENTS information_schema.EVENTS 1 267FILES information_schema.FILES 1 268GEOMETRY_COLUMNS information_schema.GEOMETRY_COLUMNS 1 269GLOBAL_STATUS information_schema.GLOBAL_STATUS 1 270GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1 271INDEX_STATISTICS information_schema.INDEX_STATISTICS 1 272INNODB_BUFFER_PAGE information_schema.INNODB_BUFFER_PAGE 1 273INNODB_BUFFER_PAGE_LRU information_schema.INNODB_BUFFER_PAGE_LRU 1 274INNODB_BUFFER_POOL_STATS information_schema.INNODB_BUFFER_POOL_STATS 1 275INNODB_CMP information_schema.INNODB_CMP 1 276INNODB_CMPMEM information_schema.INNODB_CMPMEM 1 277INNODB_CMPMEM_RESET information_schema.INNODB_CMPMEM_RESET 1 278INNODB_CMP_PER_INDEX information_schema.INNODB_CMP_PER_INDEX 1 279INNODB_CMP_RESET information_schema.INNODB_CMP_RESET 1 280INNODB_LOCKS information_schema.INNODB_LOCKS 1 281INNODB_LOCK_WAITS information_schema.INNODB_LOCK_WAITS 1 282INNODB_METRICS information_schema.INNODB_METRICS 1 283INNODB_MUTEXES information_schema.INNODB_MUTEXES 1 284INNODB_SYS_COLUMNS information_schema.INNODB_SYS_COLUMNS 1 285INNODB_SYS_FIELDS information_schema.INNODB_SYS_FIELDS 1 286INNODB_SYS_FOREIGN information_schema.INNODB_SYS_FOREIGN 1 287INNODB_SYS_FOREIGN_COLS information_schema.INNODB_SYS_FOREIGN_COLS 1 288INNODB_SYS_INDEXES information_schema.INNODB_SYS_INDEXES 1 289INNODB_SYS_TABLES information_schema.INNODB_SYS_TABLES 1 290INNODB_SYS_TABLESTATS information_schema.INNODB_SYS_TABLESTATS 1 291INNODB_SYS_VIRTUAL information_schema.INNODB_SYS_VIRTUAL 1 292INNODB_TABLESPACES_ENCRYPTION information_schema.INNODB_TABLESPACES_ENCRYPTION 1 293INNODB_TABLESPACES_SCRUBBING information_schema.INNODB_TABLESPACES_SCRUBBING 1 294INNODB_TRX information_schema.INNODB_TRX 1 295KEY_CACHES information_schema.KEY_CACHES 1 296KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1 297PARAMETERS information_schema.PARAMETERS 1 298PARTITIONS information_schema.PARTITIONS 1 299PLUGINS information_schema.PLUGINS 1 300PROCESSLIST information_schema.PROCESSLIST 1 301PROFILING information_schema.PROFILING 1 302REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 303ROUTINES information_schema.ROUTINES 1 304SCHEMATA information_schema.SCHEMATA 1 305SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 306SESSION_STATUS information_schema.SESSION_STATUS 1 307SESSION_VARIABLES information_schema.SESSION_VARIABLES 1 308SPATIAL_REF_SYS information_schema.SPATIAL_REF_SYS 1 309STATISTICS information_schema.STATISTICS 1 310SYSTEM_VARIABLES information_schema.SYSTEM_VARIABLES 1 311TABLES information_schema.TABLES 1 312TABLESPACES information_schema.TABLESPACES 1 313TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1 314TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1 315TABLE_STATISTICS information_schema.TABLE_STATISTICS 1 316TRIGGERS information_schema.TRIGGERS 1 317USER_PRIVILEGES information_schema.USER_PRIVILEGES 1 318USER_STATISTICS information_schema.USER_STATISTICS 1 319VIEWS information_schema.VIEWS 1 320+---------------------------------------+ 321+---------------------------------------+ 322+---------------------------------------+ 323Database: information_schema 324| Tables | 325| ALL_PLUGINS | 326| APPLICABLE_ROLES | 327| CHARACTER_SETS | 328| CHECK_CONSTRAINTS | 329| CLIENT_STATISTICS | 330| COLLATIONS | 331| COLLATION_CHARACTER_SET_APPLICABILITY | 332| COLUMNS | 333| COLUMN_PRIVILEGES | 334| ENABLED_ROLES | 335| ENGINES | 336| EVENTS | 337| FILES | 338| GEOMETRY_COLUMNS | 339| GLOBAL_STATUS | 340| GLOBAL_VARIABLES | 341| INDEX_STATISTICS | 342| INNODB_BUFFER_PAGE | 343| INNODB_BUFFER_PAGE_LRU | 344| INNODB_BUFFER_POOL_STATS | 345| INNODB_CMP | 346| INNODB_CMPMEM | 347| INNODB_CMPMEM_RESET | 348| INNODB_CMP_PER_INDEX | 349| INNODB_CMP_RESET | 350| INNODB_LOCKS | 351| INNODB_LOCK_WAITS | 352| INNODB_METRICS | 353| INNODB_MUTEXES | 354| INNODB_SYS_COLUMNS | 355| INNODB_SYS_FIELDS | 356| INNODB_SYS_FOREIGN | 357| INNODB_SYS_FOREIGN_COLS | 358| INNODB_SYS_INDEXES | 359| INNODB_SYS_TABLES | 360| INNODB_SYS_TABLESTATS | 361| INNODB_SYS_VIRTUAL | 362| INNODB_TABLESPACES_ENCRYPTION | 363| INNODB_TABLESPACES_SCRUBBING | 364| INNODB_TRX | 365| KEYWORDS | 366| KEY_CACHES | 367| KEY_COLUMN_USAGE | 368| PARAMETERS | 369| PARTITIONS | 370| PLUGINS | 371| PROCESSLIST | 372| PROFILING | 373| REFERENTIAL_CONSTRAINTS | 374| ROUTINES | 375| SCHEMATA | 376| SCHEMA_PRIVILEGES | 377| SESSION_STATUS | 378| SESSION_VARIABLES | 379| SPATIAL_REF_SYS | 380| SQL_FUNCTIONS | 381| STATISTICS | 382| SYSTEM_VARIABLES | 383| TABLES | 384| TABLESPACES | 385| TABLE_CONSTRAINTS | 386| TABLE_PRIVILEGES | 387| TABLE_STATISTICS | 388| TRIGGERS | 389| USER_PRIVILEGES | 390| USER_STATISTICS | 391| VIEWS | 392+---------------------------------------+ 393+---------------------------------------+ 394+---------------------------------------+ 395Database: INFORMATION_SCHEMA 396| Tables | 397| ALL_PLUGINS | 398| APPLICABLE_ROLES | 399| CHARACTER_SETS | 400| CHECK_CONSTRAINTS | 401| CLIENT_STATISTICS | 402| COLLATIONS | 403| COLLATION_CHARACTER_SET_APPLICABILITY | 404| COLUMNS | 405| COLUMN_PRIVILEGES | 406| ENABLED_ROLES | 407| ENGINES | 408| EVENTS | 409| FILES | 410| GEOMETRY_COLUMNS | 411| GLOBAL_STATUS | 412| GLOBAL_VARIABLES | 413| INDEX_STATISTICS | 414| INNODB_BUFFER_PAGE | 415| INNODB_BUFFER_PAGE_LRU | 416| INNODB_BUFFER_POOL_STATS | 417| INNODB_CMP | 418| INNODB_CMPMEM | 419| INNODB_CMPMEM_RESET | 420| INNODB_CMP_PER_INDEX | 421| INNODB_CMP_RESET | 422| INNODB_LOCKS | 423| INNODB_LOCK_WAITS | 424| INNODB_METRICS | 425| INNODB_MUTEXES | 426| INNODB_SYS_COLUMNS | 427| INNODB_SYS_FIELDS | 428| INNODB_SYS_FOREIGN | 429| INNODB_SYS_FOREIGN_COLS | 430| INNODB_SYS_INDEXES | 431| INNODB_SYS_TABLES | 432| INNODB_SYS_TABLESTATS | 433| INNODB_SYS_VIRTUAL | 434| INNODB_TABLESPACES_ENCRYPTION | 435| INNODB_TABLESPACES_SCRUBBING | 436| INNODB_TRX | 437| KEYWORDS | 438| KEY_CACHES | 439| KEY_COLUMN_USAGE | 440| PARAMETERS | 441| PARTITIONS | 442| PLUGINS | 443| PROCESSLIST | 444| PROFILING | 445| REFERENTIAL_CONSTRAINTS | 446| ROUTINES | 447| SCHEMATA | 448| SCHEMA_PRIVILEGES | 449| SESSION_STATUS | 450| SESSION_VARIABLES | 451| SPATIAL_REF_SYS | 452| SQL_FUNCTIONS | 453| STATISTICS | 454| SYSTEM_VARIABLES | 455| TABLES | 456| TABLESPACES | 457| TABLE_CONSTRAINTS | 458| TABLE_PRIVILEGES | 459| TABLE_STATISTICS | 460| TRIGGERS | 461| USER_PRIVILEGES | 462| USER_STATISTICS | 463| VIEWS | 464+--------------------+ 465+--------------------+ 466+--------------------+ 467Wildcard: inf_rmation_schema 468| Databases | 469| information_schema | 470SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; 471table_schema count(*) 472information_schema 67 473mysql 31 474