INFORMATION_SCHEMA in SQL: Purpose and Use
💡 Concept Name
INFORMATION_SCHEMA – A standardized set of read-only views that expose metadata about the database's structure, such as tables, columns, constraints, and more.
📘 Quick Intro
The INFORMATION_SCHEMA is a special schema that provides views to access database metadata in a vendor-independent and structured way. It helps developers and administrators introspect the schema without relying on DB-specific system tables.
🧠 Analogy / Short Story
Imagine a city library where instead of searching every shelf for books, you consult a catalog that lists all book titles, authors, and genres. INFORMATION_SCHEMA is that catalog — giving you instant access to everything about your database’s structure without digging into internals.
🔧 Technical Explanation
- 📊
INFORMATION_SCHEMAis a system-defined schema that contains read-only views about your database metadata. - 📁 Includes views like
TABLES,COLUMNS,CONSTRAINTS,SCHEMATA, etc. - 🔍 Helps in dynamically inspecting table names, column types, constraints, default values, etc.
- ⚙️ Supports ANSI SQL standards, so it's portable across databases like MySQL, SQL Server, PostgreSQL, and more.
- 🚫 You can’t modify data through it — it's strictly for querying schema metadata.
💻 Real Code Example
-- List all tables in the current database
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'dbo';
-- Get column details for a specific table
SELECT column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Customers';
-- Find primary key constraints
SELECT constraint_name, table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = 'PRIMARY KEY';
❓ Interview Q&A
Q1: What is INFORMATION_SCHEMA in SQL?
A: It's a standardized set of views that expose metadata about the database structure like tables, columns, and constraints.
Q2: Why is INFORMATION_SCHEMA useful?
A: It allows developers to introspect database structure dynamically without using database-specific system tables.
Q3: Can you modify data using INFORMATION_SCHEMA views?
A: No, they are read-only views meant only for querying metadata.
Q4: Is INFORMATION_SCHEMA supported across all RDBMS?
A: Most major RDBMS like SQL Server, MySQL, and PostgreSQL support it, though implementation may vary slightly.
Q5: How do you list all tables in a schema using INFORMATION_SCHEMA?
A: Use the TABLES view and filter by TABLE_SCHEMA.
Q6: What is the difference between sys.tables and INFORMATION_SCHEMA.TABLES in SQL Server?
A: sys.tables is SQL Server-specific; INFORMATION_SCHEMA.TABLES is standard and portable.
Q7: How do you check column data types in a table?
A: Query INFORMATION_SCHEMA.COLUMNS and filter by table name.
Q8: What does the TABLE_CONSTRAINTS view show?
A: It lists constraints like PRIMARY KEY, FOREIGN KEY, and UNIQUE applied to tables.
Q9: Can you get default values of columns using INFORMATION_SCHEMA?
A: Yes, via the COLUMNS view, though some RDBMS may limit this.
Q10: What is a common use case for INFORMATION_SCHEMA?
A: Generating dynamic reports, building database documentation, and performing schema analysis.
📝 MCQs
Q1. What does INFORMATION_SCHEMA provide?
- Query execution plans
- Trigger logs
- Database metadata views
- Schema backups
Q2. Is INFORMATION_SCHEMA read-only?
- Yes
- No
- Only in MySQL
- Only in PostgreSQL
Q3. Which view lists all tables?
- COLUMNS
- OBJECTS
- TABLES
- SCHEMATA
Q4. What is the benefit of INFORMATION_SCHEMA?
- Faster inserts
- Portability across RDBMS
- Stored procedures access
- Triggers listing
Q5. Which constraint type is listed in TABLE_CONSTRAINTS?
- GROUP BY
- SELECT DISTINCT
- PRIMARY KEY
- ORDER BY
Q6. How do you find column types using INFORMATION_SCHEMA?
- Use sys.columns
- Query COLUMNS view
- Join tables
- Check logs
Q7. Can INFORMATION_SCHEMA be used to update tables?
- Yes
- No
- Sometimes
- Only with GRANT
Q8. Which schema contains standardized views?
- DBO
- ADMIN
- INFORMATION_SCHEMA
- PUBLIC
Q9. Which databases support INFORMATION_SCHEMA?
- Only SQL Server
- Only PostgreSQL
- Most major RDBMS
- Only Oracle
Q10. What does the SCHEMATA view show?
- Available schemas
- Triggers
- Functions
- Backup history
💡 Bonus Insight
Using INFORMATION_SCHEMA helps maintain cross-database compatibility in applications, especially when building tools that must work on multiple RDBMS systems. However, some databases like Oracle do not fully support it, so test accordingly.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!