Difficulty: 2/10SHOW TABLES, INFORMATION_SCHEMA, metadata queriesWe use the SHOW TABLES command to retrieve the names of tables that are present in a specific database.Query:CopyScenario Questions0-2 years experience1You have a fresh MySQL instance and need to see what tables exist in the current database. What command would you run and how would you confirm the output?2If you execute SHOW TABLES and see no rows, what are the possible explanations?3How would you list only the tables that belong to a specific schema using a query against MySQL's metadata?2-5 years experience1Your app crashes because it expects a table that isn’t present. Walk me through how you would verify the table’s existence in MySQL and what steps you’d take if it’s missing.2You need to generate a list of all tables that start with the prefix 'audit_'. Which MySQL metadata source would you use and why might SHOW TABLES alone be insufficient?3During a deployment a teammate reports that a newly created table isn’t visible on the read replica. How would you check the replica’s tables and what could cause this discrepancy?5-8 years experience1In a high‑traffic service we cache table metadata to avoid repeated SHOW TABLES calls. Discuss the trade‑offs of caching versus querying INFORMATION_SCHEMA each time, including consistency and performance considerations.2You’re building a tool that audits schema drift across dozens of MySQL instances. How would you design the component that enumerates tables while handling permissions, large table counts, and minimal impact on production workloads?3Explain how MySQL’s metadata locking behaves when you list tables, and what problems could arise if a long‑running ALTER TABLE is happening concurrently.8+ years experience1Our organization is consolidating many independent MySQL databases into a shared data lake. How would you migrate existing table‑listing logic to ensure downstream services continue to discover tables reliably, considering versioning, backward compatibility, and cross‑team ownership?2Design a centralized schema registry service that provides table lists for multiple teams. What APIs, caching strategies, and security models would you propose, and how would you handle schema changes without breaking existing consumers?3When refactoring legacy code you discover some modules use SHOW TABLES while others query INFORMATION_SCHEMA. What strategy would you adopt to standardize metadata access across the codebase, and how would you manage the transition with minimal disruption?Follow-up QuestionsWhat privileges does a user need to list tables in MySQL?If SHOW TABLES returns an empty set, what could be the underlying reasons?How does querying INFORMATION_SCHEMA impact performance on a large instance?Suggest Improvement
