<div class="mysql-notes">
<h1>Viewing Table Structures Using DESCRIBE and SHOW COLUMNS in MySQL – Detailed Notes</h1>
<p><strong>Course:</strong> MySQL Training Course</p>
<p><strong>Topic:</strong> Viewing Table Structures Using DESCRIBE and SHOW COLUMNS</p>
<p><strong>Course Link:</strong> <a href="https://www.justacademy.co/course-detail/mysql-training" target="_blank" rel="noopener noreferrer">MySQL Training Course</a></p>
<p><strong>Demo Link:</strong> <a href="https://www.justacademy.co/register-for-course-demo" target="_blank" rel="noopener noreferrer">Register for Course Demo</a></p>
<hr>
<h2>1. Introduction to Viewing Table Structures in MySQL</h2>
<p>In MySQL, viewing a table structure means examining the definition of a table, including its column names, data types, NULL restrictions, keys, default values, and additional attributes. Understanding a table's structure is an important part of database development, maintenance, debugging, and data analysis.</p>
<p>MySQL provides the <code>DESCRIBE</code> statement and the <code>SHOW COLUMNS</code> statement to display information about table columns. Both commands provide similar information and are commonly used when working with an existing database.</p>
<p>For example, before inserting employee records, a developer may need to check whether the employee table contains columns such as employee_id, employee_name, salary, and department. The DESCRIBE statement can display these details without requiring the developer to inspect every record.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>Another way to retrieve similar information is:</p>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<p>These commands help developers understand how a table is organized before writing SELECT, INSERT, UPDATE, or DELETE statements.</p>
<h2>2. Learning Objectives</h2>
<p>After completing this topic, you will be able to:</p>
<ul>
<li>Understand what table structure means in MySQL.</li>
<li>Explain the purpose of DESCRIBE and SHOW COLUMNS.</li>
<li>Display the columns and data types of a table.</li>
<li>Understand the Field, Type, Null, Key, Default, and Extra columns in the output.</li>
<li>Use DESC as a shortcut for DESCRIBE.</li>
<li>Inspect a table in the current database or another database.</li>
<li>Filter column information using LIKE and WHERE clauses.</li>
<li>Use SHOW FULL COLUMNS to inspect additional column details.</li>
<li>Understand the difference between DESCRIBE, SHOW COLUMNS, and SHOW CREATE TABLE.</li>
<li>Apply table-inspection commands when troubleshooting database problems.</li>
</ul>
<h2>3. What Is a Table Structure?</h2>
<p>A table structure defines how information is organized in a MySQL table. It specifies the columns available, the type of data each column stores, whether values can be NULL, and whether constraints or special attributes apply.</p>
<p>Consider an employee table containing the following columns:</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Column Name</th><th>Data Type</th><th>Purpose</th></tr>
<tr><td>employee_id</td><td>INT</td><td>Stores the employee identifier.</td></tr>
<tr><td>employee_name</td><td>VARCHAR(100)</td><td>Stores the employee's name.</td></tr>
<tr><td>department</td><td>VARCHAR(50)</td><td>Stores the department name.</td></tr>
<tr><td>salary</td><td>DECIMAL(10,2)</td><td>Stores the employee's salary.</td></tr>
<tr><td>joining_date</td><td>DATE</td><td>Stores the employee's joining date.</td></tr>
</table>
<p>The table structure describes the design of the table rather than the actual employee records. Commands such as DESCRIBE and SHOW COLUMNS allow you to inspect this design.</p>
<h2>4. Understanding the DESCRIBE Statement</h2>
<p>The <strong>DESCRIBE</strong> statement displays information about the columns in a table. It is commonly used to inspect a table's structure quickly.</p>
<h3>4.1 Syntax of DESCRIBE</h3>
<pre><code>DESCRIBE table_name;</code></pre>
<p>Here, <code>table_name</code> is the name of the table whose column definitions you want to view.</p>
<h3>4.2 Basic Example</h3>
<pre><code>DESCRIBE employees;</code></pre>
<p>A typical output may look like this:</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>
<tr><td>employee_id</td><td>int</td><td>NO</td><td>PRI</td><td>NULL</td><td>auto_increment</td></tr>
<tr><td>employee_name</td><td>varchar(100)</td><td>NO</td><td></td><td>NULL</td><td></td></tr>
<tr><td>department</td><td>varchar(50)</td><td>YES</td><td></td><td>NULL</td><td></td></tr>
<tr><td>salary</td><td>decimal(10,2)</td><td>YES</td><td></td><td>NULL</td><td></td></tr>
<tr><td>joining_date</td><td>date</td><td>YES</td><td></td><td>NULL</td><td></td></tr>
</table>
<p>This is an illustrative output based on a possible table definition. The actual results depend on the table you inspect.</p>
<h3>4.3 Using DESC as a Shortcut</h3>
<p>MySQL supports <code>DESC</code> as a shorter form of DESCRIBE when inspecting table columns.</p>
<pre><code>DESC employees;</code></pre>
<p>This command provides the same type of column information as:</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>Using DESC is convenient when you need to check table definitions frequently in the MySQL command-line client or a database management tool.</p>
<h3>4.4 Describing a Table in a Specific Database</h3>
<p>If the required table belongs to a database other than the currently selected database, specify the database name with the table name.</p>
<pre><code>DESCRIBE company_db.employees;</code></pre>
<p>This command requests the structure of the employees table in the company_db database.</p>
<h2>5. Understanding the SHOW COLUMNS Statement</h2>
<p>The <strong>SHOW COLUMNS</strong> statement displays information about the columns in a table or view. Its output is similar to DESCRIBE, and it offers additional options for filtering column information.</p>
<h3>5.1 Syntax of SHOW COLUMNS</h3>
<pre><code>SHOW COLUMNS FROM table_name;</code></pre>
<p>The word FIELDS can also be used instead of COLUMNS.</p>
<pre><code>SHOW FIELDS FROM table_name;</code></pre>
<h3>5.2 Basic Example</h3>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<p>The result contains column names, data types, NULL information, key information, default values, and additional attributes.</p>
<h3>5.3 Specifying a Database</h3>
<p>You can use a database-qualified table name:</p>
<pre><code>SHOW COLUMNS FROM company_db.employees;</code></pre>
<p>MySQL also supports this form:</p>
<pre><code>SHOW COLUMNS FROM employees FROM company_db;</code></pre>
<p>Both forms request column information for the employees table in company_db.</p>
<h3>5.4 Using SHOW FULL COLUMNS</h3>
<p>The FULL option displays additional information, including collation, column privileges, and comments.</p>
<pre><code>SHOW FULL COLUMNS FROM employees;</code></pre>
<p>This is useful when examining character columns, documentation comments, and other column-level metadata.</p>
<h2>6. Understanding the Output Columns</h2>
<p>DESCRIBE and the standard SHOW COLUMNS command usually display six main fields: Field, Type, Null, Key, Default, and Extra.</p>
<h3>6.1 Field</h3>
<p>The Field column contains the name of the table column.</p>
<p>For example, an employees table may contain employee_id, employee_name, salary, and joining_date.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>Use the Field column to confirm the exact spelling of a column name before writing a query.</p>
<h3>6.2 Type</h3>
<p>The Type column indicates the column's data type and may include details such as length, precision, and scale.</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Example Type</th><th>Purpose</th></tr>
<tr><td>INT</td><td>Stores integer values.</td></tr>
<tr><td>VARCHAR(100)</td><td>Stores variable-length character strings.</td></tr>
<tr><td>DECIMAL(10,2)</td><td>Stores fixed-point decimal numbers.</td></tr>
<tr><td>DATE</td><td>Stores calendar dates.</td></tr>
<tr><td>DATETIME</td><td>Stores date and time values.</td></tr>
<tr><td>TEXT</td><td>Stores longer text values.</td></tr>
</table>
<p>Checking Type helps you determine which values can be stored and which comparisons or calculations are appropriate.</p>
<h3>6.3 Null</h3>
<p>The Null field indicates whether the column permits NULL values.</p>
<ul>
<li><strong>YES:</strong> The column can accept NULL values.</li>
<li><strong>NO:</strong> The column is defined as NOT NULL.</li>
</ul>
<p>For example, a required employee name may be defined as NOT NULL, while a department field may allow NULL if the department is not yet known.</p>
<h3>6.4 Key</h3>
<p>The Key field indicates certain key or index information about the column.</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Value</th><th>Meaning</th></tr>
<tr><td>PRI</td><td>The column is part of a primary key.</td></tr>
<tr><td>UNI</td><td>The column is the first column of a unique index.</td></tr>
<tr><td>MUL</td><td>The column is the first column of a nonunique index, or is represented by certain composite-index situations.</td></tr>
<tr><td>Blank</td><td>No applicable key indicator is shown for that column in this output.</td></tr>
</table>
<p>The Key field is a useful summary, but it does not show every detail of every index. Use SHOW INDEX FROM table_name when you need more complete index information.</p>
<h3>6.5 Default</h3>
<p>The Default field displays the column's default value, if applicable.</p>
<p>For example, a column might use a default status of Active:</p>
<pre><code>CREATE TABLE employee_status_demo (
employee_id INT PRIMARY KEY,
status VARCHAR(20) NOT NULL DEFAULT 'Active'
);
DESCRIBE employee_status_demo;</code></pre>
<p>When an INSERT statement omits the status column, MySQL can use the specified default value.</p>
<p>A displayed NULL in the Default column does not always mean that the column permits NULL. Check the Null field separately to determine whether NULL values are allowed.</p>
<h3>6.6 Extra</h3>
<p>The Extra field displays additional attributes associated with a column.</p>
<p>Examples include:</p>
<ul>
<li><code>auto_increment</code> for an automatically incrementing integer column.</li>
<li><code>on update CURRENT_TIMESTAMP</code> for certain timestamp or datetime columns configured to update automatically.</li>
<li>Generated-column information such as <code>VIRTUAL GENERATED</code> or <code>STORED GENERATED</code>.</li>
<li>Other additional metadata where applicable.</li>
</ul>
<p>Example:</p>
<pre><code>CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
DESCRIBE departments;</code></pre>
<p>The department_id column typically displays <code>auto_increment</code> in the Extra field.</p>
<h2>7. Creating a Sample Database and Table</h2>
<p>Before practicing the table-inspection commands, create a database and an employees table.</p>
<pre><code>CREATE DATABASE mysql_structure_demo;
USE mysql_structure_demo;
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2) DEFAULT 0.00,
joining_date DATE,
employment_status VARCHAR(20) DEFAULT 'Active'
);</code></pre>
<p>The table includes several data types and attributes so that the DESCRIBE and SHOW COLUMNS outputs contain different types of metadata.</p>
<h3>7.1 Insert Sample Records</h3>
<pre><code>INSERT INTO employees
(employee_name, department, salary, joining_date, employment_status)
VALUES
('Aarav Sharma', 'IT', 65000.00, '2025-01-15', 'Active'),
('Priya Mehta', 'HR', 48000.00, '2024-08-10', 'Active'),
('Rahul Verma', 'Sales', 55000.00, '2023-11-20', 'Inactive');</code></pre>
<p>Inserting records is not required to inspect a table structure. These records are included to demonstrate that table definitions and table data are separate concepts.</p>
<h2>8. Practical Examples Using DESCRIBE</h2>
<h3>8.1 View All Columns</h3>
<pre><code>DESCRIBE employees;</code></pre>
<p>Use this statement when you want a quick overview of every column in the employees table.</p>
<h3>8.2 Use the DESC Shortcut</h3>
<pre><code>DESC employees;</code></pre>
<p>This is a shorter alternative that returns similar structural information.</p>
<h3>8.3 Describe a Column Using Its Name</h3>
<p>You can request information about a particular column by specifying its name.</p>
<pre><code>DESCRIBE employees employee_name;</code></pre>
<p>This returns the structure information for the employee_name column rather than listing every column.</p>
<h3>8.4 Filter Column Names with a Pattern</h3>
<p>DESCRIBE supports a pattern argument that can be used to retrieve columns whose names match a pattern.</p>
<pre><code>DESCRIBE employees 'employee%';</code></pre>
<p>The percent sign represents zero or more characters. This pattern can match columns such as employee_id and employee_name.</p>
<h2>9. Practical Examples Using SHOW COLUMNS</h2>
<h3>9.1 Display All Columns</h3>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<p>This command displays the column definitions in the selected table.</p>
<h3>9.2 Display Columns from Another Database</h3>
<pre><code>SHOW COLUMNS FROM mysql_structure_demo.employees;</code></pre>
<p>Use a database-qualified table name when you want to inspect a table without changing the currently selected database.</p>
<h3>9.3 Display Full Column Information</h3>
<pre><code>SHOW FULL COLUMNS FROM employees;</code></pre>
<p>This command provides extra metadata, including character collation and column comments where applicable.</p>
<h3>9.4 Display Columns Matching a LIKE Pattern</h3>
<p>The LIKE clause filters column names by a pattern.</p>
<pre><code>SHOW COLUMNS FROM employees LIKE 'employee%';</code></pre>
<p>This returns column names beginning with employee, such as employee_id and employee_name.</p>
<p>To find column names ending in date, use:</p>
<pre><code>SHOW COLUMNS FROM employees LIKE '%date';</code></pre>
<h3>9.5 Display Columns Using an Underscore Wildcard</h3>
<p>The underscore wildcard represents exactly one character in a LIKE pattern.</p>
<pre><code>SHOW COLUMNS FROM employees LIKE 'salary_';</code></pre>
<p>This pattern matches a column name consisting of salary followed by exactly one character. It would not match the column named salary because that name has no additional character after salary.</p>
<h3>9.6 Filter Column Metadata with WHERE</h3>
<p>SHOW COLUMNS supports a WHERE clause for more flexible filtering of its output.</p>
<pre><code>SHOW COLUMNS FROM employees
WHERE Type LIKE 'varchar%';</code></pre>
<p>This example selects the columns whose displayed Type begins with varchar.</p>
<p>You can also filter by the Null field:</p>
<pre><code>SHOW COLUMNS FROM employees
WHERE `Null` = 'NO';</code></pre>
<p>Backticks are used around Null because it is also a MySQL keyword in other contexts. The query returns columns that do not permit NULL values.</p>
<h2>10. Difference Between DESCRIBE and SHOW COLUMNS</h2>
<p>DESCRIBE and SHOW COLUMNS provide similar column information. DESCRIBE is usually the most concise option for quick inspection, while SHOW COLUMNS provides filtering options that are useful for more targeted inspection.</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Feature</th><th>DESCRIBE</th><th>SHOW COLUMNS</th></tr>
<tr><td>Primary purpose</td><td>Displays table column definitions.</td><td>Displays table column definitions.</td></tr>
<tr><td>Basic syntax</td><td><code>DESCRIBE employees;</code></td><td><code>SHOW COLUMNS FROM employees;</code></td></tr>
<tr><td>Short form</td><td><code>DESC employees;</code></td><td>No equivalent DESC shortcut is needed.</td></tr>
<tr><td>Column filtering</td><td>Supports a column name or pattern.</td><td>Supports LIKE and WHERE filtering.</td></tr>
<tr><td>Additional metadata</td><td>Provides the standard structural output.</td><td>SHOW FULL COLUMNS adds collation, privileges, and comments.</td></tr>
<tr><td>Database selection</td><td>Supports a database-qualified table name.</td><td>Supports a database-qualified name or database clause.</td></tr>
<tr><td>Typical use</td><td>Quick inspection of a table.</td><td>Inspection with additional filtering options.</td></tr>
</table>
<h2>11. Difference Between DESCRIBE, SHOW COLUMNS, and SHOW CREATE TABLE</h2>
<p>These statements provide different levels of information about a table.</p>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Command</th><th>Information Provided</th><th>Example</th></tr>
<tr><td>DESCRIBE</td><td>Column names, types, NULL information, keys, defaults, and extra attributes.</td><td><code>DESCRIBE employees;</code></td></tr>
<tr><td>SHOW COLUMNS</td><td>Similar column information, with options for filtering.</td><td><code>SHOW COLUMNS FROM employees;</code></td></tr>
<tr><td>SHOW FULL COLUMNS</td><td>Column information with additional metadata such as collation and comments.</td><td><code>SHOW FULL COLUMNS FROM employees;</code></td></tr>
<tr><td>SHOW CREATE TABLE</td><td>The SQL statement that defines the table, including its columns and applicable constraints and options.</td><td><code>SHOW CREATE TABLE employees;</code></td></tr>
<tr><td>SHOW INDEX</td><td>Detailed information about indexes defined on the table.</td><td><code>SHOW INDEX FROM employees;</code></td></tr>
</table>
<p>Use DESCRIBE or SHOW COLUMNS when you mainly need a column summary. Use SHOW CREATE TABLE when you need to inspect the table's complete creation definition, and SHOW INDEX when you need details about indexes.</p>
<h2>12. Using SHOW TABLES Before DESCRIBE</h2>
<p>If you do not know the table name, use SHOW TABLES to list the tables in the currently selected database.</p>
<pre><code>USE mysql_structure_demo;
SHOW TABLES;</code></pre>
<p>After identifying the table, inspect its structure.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>This is a common workflow when working with an unfamiliar database or exploring a database created by another developer.</p>
<h2>13. Inspecting Table Structures Without Selecting a Database</h2>
<p>You do not always need to execute USE before inspecting a table. You can include the database name directly in the command.</p>
<pre><code>DESCRIBE mysql_structure_demo.employees;</code></pre>
<p>The equivalent SHOW COLUMNS statement is:</p>
<pre><code>SHOW COLUMNS FROM mysql_structure_demo.employees;</code></pre>
<p>This is helpful when you work with multiple databases or want to inspect a table in a different schema without changing the active database context.</p>
<h2>14. Understanding Keys and Indexes in the Output</h2>
<p>The Key field in DESCRIBE and SHOW COLUMNS provides a summary of certain index information. Understanding these indicators helps you interpret how a table identifies records and supports lookups.</p>
<h3>14.1 Primary Key</h3>
<p>A primary key uniquely identifies each row in a table and cannot contain NULL values.</p>
<pre><code>CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
);
DESCRIBE courses;</code></pre>
<p>The course_id column is normally marked PRI in the Key field.</p>
<h3>14.2 Unique Key</h3>
<p>A unique index prevents duplicate combinations of indexed values, subject to MySQL's NULL rules.</p>
<pre><code>CREATE TABLE students (
student_id INT PRIMARY KEY,
email VARCHAR(150) UNIQUE
);
DESCRIBE students;</code></pre>
<p>The email column may be marked UNI because it is the first column of a unique index.</p>
<h3>14.3 Nonunique Index</h3>
<p>A nonunique index can improve lookup performance while allowing repeated values.</p>
<pre><code>CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
INDEX idx_customer_id (customer_id)
);
DESCRIBE orders;</code></pre>
<p>The customer_id column may be marked MUL because it is the first column of a nonunique index.</p>
<p>For complete information about index definitions, use:</p>
<pre><code>SHOW INDEX FROM orders;</code></pre>
<h2>15. Using Table Structure Information for Troubleshooting</h2>
<p>DESCRIBE and SHOW COLUMNS are useful when a query fails because a column name, data type, or constraint does not match expectations.</p>
<h3>15.1 Checking an Unknown Column Error</h3>
<p>If a query produces an unknown-column error, first inspect the table structure.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>Confirm the exact column name and use that name in the query. For example, a table may contain employee_name rather than name.</p>
<h3>15.2 Checking a Data Type Mismatch</h3>
<p>If a query attempts to perform arithmetic on a column, inspect the Type field to confirm whether it is numeric or character-based.</p>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<p>For example, a salary column defined as DECIMAL is more suitable for monetary calculations than a column defined as VARCHAR.</p>
<h3>15.3 Checking NULL Restrictions</h3>
<p>If an INSERT statement fails because a required column has no value, inspect the Null and Default fields.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>A column marked NO in the Null field does not permit NULL values. If the column has no usable default, the INSERT statement must provide an appropriate value.</p>
<h3>15.4 Checking AUTO_INCREMENT</h3>
<p>If you expect MySQL to generate an identifier automatically, inspect the Extra field.</p>
<pre><code>DESCRIBE employees;</code></pre>
<p>Check whether the identifier column displays auto_increment.</p>
<h3>15.5 Checking Column Comments and Collation</h3>
<p>When investigating text comparison or documentation issues, use SHOW FULL COLUMNS.</p>
<pre><code>SHOW FULL COLUMNS FROM employees;</code></pre>
<p>Collation can affect how strings are compared and sorted. Column comments can also provide useful documentation when they have been defined.</p>
<h2>16. Permissions and Visibility</h2>
<p>MySQL displays column information only for objects and columns that the current user is permitted to access. If expected information is missing or a command fails, verify that the table exists and that the account has appropriate privileges.</p>
<p>For example, list the tables available in the selected database:</p>
<pre><code>SHOW TABLES;</code></pre>
<p>Then inspect the relevant table:</p>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<p>Do not assume that a missing result always means a column does not exist. Check the selected database, object name, and account permissions.</p>
<h2>17. Common Mistakes and How to Avoid Them</h2>
<table border="1" cellpadding="8" cellspacing="0">
<tr><th>Mistake</th><th>Why It Is a Problem</th><th>Recommended Solution</th></tr>
<tr><td>Forgetting the table name</td><td>The statement cannot identify which table to inspect.</td><td>Specify the table name after DESCRIBE or FROM.</td></tr>
<tr><td>Using SHOW COLUMNS without FROM</td><td>The syntax is incomplete for normal table inspection.</td><td>Use <code>SHOW COLUMNS FROM table_name;</code>.</td></tr>
<tr><td>Inspecting the wrong database</td><td>A table with the same name may have a different structure in another database.</td><td>Check the selected database or use a database-qualified table name.</td></tr>
<tr><td>Confusing structure with data</td><td>DESCRIBE does not display the actual table records.</td><td>Use SELECT to retrieve table data.</td></tr>
<tr><td>Assuming Default NULL means NULL is allowed</td><td>Default and nullability are separate properties.</td><td>Check the Null field as well.</td></tr>
<tr><td>Assuming Key shows every index detail</td><td>The Key field is only a summary.</td><td>Use SHOW INDEX for detailed index information.</td></tr>
<tr><td>Expecting SHOW COLUMNS to show the complete CREATE statement</td><td>It focuses on column metadata rather than the full table definition.</td><td>Use SHOW CREATE TABLE.</td></tr>
<tr><td>Ignoring privileges</td><td>Metadata may not be visible to the current account.</td><td>Verify permissions and object access.</td></tr>
</table>
<h2>18. Best Practices for Viewing Table Structures</h2>
<ul>
<li>Use DESCRIBE for a quick overview of table columns.</li>
<li>Use DESC when a shorter command is convenient.</li>
<li>Use SHOW COLUMNS when you need to filter the displayed column information.</li>
<li>Use SHOW FULL COLUMNS to inspect additional metadata such as collation and comments.</li>
<li>Use SHOW CREATE TABLE when you need the full table definition.</li>
<li>Use SHOW INDEX when you need detailed index information.</li>
<li>Check the database name before inspecting a table.</li>
<li>Verify column names and types before writing complex queries.</li>
<li>Inspect Null, Default, and Extra when troubleshooting insert or update problems.</li>
<li>Use appropriate database privileges rather than unnecessarily granting broad access.</li>
<li>Remember that DESCRIBE shows structure, while SELECT retrieves stored rows.</li>
</ul>
<h2>19. Practical Workflow for Inspecting a Table</h2>
<ol>
<li><strong>Select the database:</strong> Use USE database_name when necessary.</li>
<li><strong>List available tables:</strong> Run SHOW TABLES if you do not know the table name.</li>
<li><strong>Inspect the table:</strong> Run DESCRIBE table_name.</li>
<li><strong>Check individual columns:</strong> Review Field, Type, Null, Key, Default, and Extra.</li>
<li><strong>Filter when needed:</strong> Use SHOW COLUMNS with LIKE or WHERE to find specific columns.</li>
<li><strong>Inspect extended metadata:</strong> Run SHOW FULL COLUMNS when collation or comments are relevant.</li>
<li><strong>Check the full definition:</strong> Use SHOW CREATE TABLE for constraints and table options.</li>
<li><strong>Check indexes:</strong> Use SHOW INDEX if you need more detailed index information.</li>
<li><strong>Write and test queries:</strong> Use the inspected schema to choose correct column names and data types.</li>
</ol>
<h2>20. Practice Exercises</h2>
<h3>Exercise 1: Display a Table Structure</h3>
<p>Write a command to display all columns in the employees table.</p>
<pre><code>DESCRIBE employees;</code></pre>
<h3>Exercise 2: Use the DESC Shortcut</h3>
<p>Display the same table structure using the shorter form.</p>
<pre><code>DESC employees;</code></pre>
<h3>Exercise 3: Use SHOW COLUMNS</h3>
<p>Display column information for the employees table.</p>
<pre><code>SHOW COLUMNS FROM employees;</code></pre>
<h3>Exercise 4: Display Full Column Metadata</h3>
<p>Show additional information about the columns.</p>
<pre><code>SHOW FULL COLUMNS FROM employees;</code></pre>
<h3>Exercise 5: Filter Column Names</h3>
<p>Display only columns whose names begin with employee.</p>
<pre><code>SHOW COLUMNS FROM employees LIKE 'employee%';</code></pre>
<h3>Exercise 6: Inspect a Table in Another Database</h3>
<p>Display the structure of the employees table in mysql_structure_demo.</p>
<pre><code>DESCRIBE mysql_structure_demo.employees;</code></pre>
<h3>Exercise 7: Display the Full Table Definition</h3>
<p>Retrieve the SQL definition used to create the employees table.</p>
<pre><code>SHOW CREATE TABLE employees;</code></pre>
<h3>Exercise 8: Inspect Indexes</h3>
<p>Display index information for the employees table.</p>
<pre><code>SHOW INDEX FROM employees;</code></pre>
<h2>21. Mini Project: Database Schema Inspection</h2>
<p><strong>Project Objective:</strong> Inspect a small database and document the structure of its tables using DESCRIBE and SHOW COLUMNS.</p>
<h3>Project Scenario</h3>
<p>You have joined a team that maintains an existing MySQL database. The database contains employees, departments, and projects tables. Before building reports, you need to understand the available columns, their types, and their constraints.</p>
<h3>Project Tasks</h3>
<ol>
<li>Create a database named company_schema_demo.</li>
<li>Create employees, departments, and projects tables.</li>
<li>Use SHOW TABLES to list the tables.</li>
<li>Run DESCRIBE on each table.</li>
<li>Run SHOW COLUMNS on each table.</li>
<li>Use SHOW FULL COLUMNS on a table containing text columns.</li>
<li>Use SHOW CREATE TABLE to inspect the table definitions.</li>
<li>Use SHOW INDEX to inspect primary keys and other indexes.</li>
<li>Record each column name, data type, nullability, key, default, and extra attribute.</li>
<li>Write a short summary explaining how the table structure affects data entry and querying.</li>
</ol>
<h3>Example Project Commands</h3>
<pre><code>CREATE DATABASE company_schema_demo;
USE company_schema_demo;
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
salary DECIMAL(10,2) DEFAULT 0.00,
joining_date DATE,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
CREATE TABLE projects (
project_id INT AUTO_INCREMENT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE,
project_status VARCHAR(30) DEFAULT 'Planned'
);
SHOW TABLES;
DESCRIBE departments;
DESCRIBE employees;
DESCRIBE projects;
SHOW COLUMNS FROM employees;
SHOW FULL COLUMNS FROM projects;
SHOW CREATE TABLE employees;
SHOW INDEX FROM employees;</code></pre>
<p>This project provides practical experience in examining table definitions before developing SQL queries or modifying an existing database.</p>
<h2>22. Interview Questions and Answers</h2>
<h3>Q1. What is the purpose of DESCRIBE in MySQL?</h3>
<p>DESCRIBE displays structural information about a table's columns, including names, data types, NULL restrictions, keys, defaults, and additional attributes.</p>
<h3>Q2. What is the syntax of DESCRIBE?</h3>
<pre><code>DESCRIBE table_name;</code></pre>
<h3>Q3. What is DESC in MySQL?</h3>
<p>DESC is a shortcut for DESCRIBE when viewing table column information.</p>
<h3>Q4. What does SHOW COLUMNS do?</h3>
<p>SHOW COLUMNS displays information about the columns in a table or view. It also supports filtering through LIKE and WHERE.</p>
<h3>Q5. Are DESCRIBE and SHOW COLUMNS similar?</h3>
<p>Yes. Both provide similar information about table columns, although SHOW COLUMNS offers additional filtering options and can be used with FULL.</p>
<h3>Q6. What does the Field column represent?</h3>
<p>Field contains the name of each column in the table.</p>
<h3>Q7. What does the Type column represent?</h3>
<p>Type displays the data type and relevant type details, such as VARCHAR length or DECIMAL precision and scale.</p>
<h3>Q8. What does the Null column mean?</h3>
<p>It indicates whether a column allows NULL values. YES means NULL is allowed, while NO means the column is defined as NOT NULL.</p>
<h3>Q9. What does PRI mean in the Key column?</h3>
<p>PRI indicates that the column is part of a primary key.</p>
<h3>Q10. What does UNI mean?</h3>
<p>UNI indicates that the column is the first column of a unique index.</p>
<h3>Q11. What does MUL mean?</h3>
<p>MUL indicates certain nonunique index situations, commonly where the column is the first column of a nonunique index.</p>
<h3>Q12. What is the purpose of SHOW FULL COLUMNS?</h3>
<p>It displays additional column metadata, including collation, privileges, and comments.</p>
<h3>Q13. How do you view a table structure in a specific database?</h3>
<pre><code>DESCRIBE database_name.table_name;</code></pre>
<h3>Q14. How do you filter column names using SHOW COLUMNS?</h3>
<pre><code>SHOW COLUMNS FROM employees LIKE 'employee%';</code></pre>
<h3>Q15. What is the difference between DESCRIBE and SHOW CREATE TABLE?</h3>
<p>DESCRIBE summarizes column information. SHOW CREATE TABLE displays the SQL definition used to create the table, including applicable constraints and table options.</p>
<h3>Q16. Does DESCRIBE show the actual records stored in a table?</h3>
<p>No. It displays the table's structure. Use SELECT to retrieve stored records.</p>
<h3>Q17. How can you inspect a table's indexes in detail?</h3>
<pre><code>SHOW INDEX FROM table_name;</code></pre>
<h3>Q18. What should you do if a table is not found?</h3>
<p>Check the selected database, verify the table name using SHOW TABLES, and confirm that the current account has permission to access the table.</p>
<h2>23. Quick Revision Notes</h2>
<ul>
<li>DESCRIBE displays table column information.</li>
<li>DESC is a shortcut for DESCRIBE.</li>
<li>SHOW COLUMNS provides similar information with filtering options.</li>
<li>SHOW FIELDS is an alternative form of SHOW COLUMNS.</li>
<li>SHOW FULL COLUMNS includes additional metadata.</li>
<li>Field represents the column name.</li>
<li>Type represents the column data type.</li>
<li>Null indicates whether NULL values are permitted.</li>
<li>Key summarizes certain key and index information.</li>
<li>Default displays the default value when applicable.</li>
<li>Extra displays additional attributes such as auto_increment.</li>
<li>SHOW CREATE TABLE displays the table definition.</li>
<li>SHOW INDEX displays detailed index information.</li>
<li>SHOW TABLES lists tables in the selected database.</li>
<li>Use SELECT when you want to retrieve actual table records.</li>
</ul>
<h2>24. Best Practices Checklist</h2>
<ul>
<li>Inspect a table before writing complex queries against an unfamiliar schema.</li>
<li>Use DESCRIBE for quick structure checks.</li>
<li>Use SHOW COLUMNS when you need filtered results.</li>
<li>Use database-qualified table names when working across multiple databases.</li>
<li>Check data types before performing calculations or comparisons.</li>
<li>Review NULL and Default fields before inserting data.</li>
<li>Do not rely only on the Key field when you need complete index details.</li>
<li>Use SHOW CREATE TABLE to examine constraints and table options.</li>
<li>Use SHOW FULL COLUMNS when column collation or comments matter.</li>
<li>Confirm that you have access to the database and table.</li>
</ul>
<h2>25. Conclusion</h2>
<p>DESCRIBE and SHOW COLUMNS are essential MySQL commands for understanding how database tables are organized. They help developers inspect column names, data types, NULL restrictions, key information, default values, and additional attributes before writing queries or troubleshooting database issues.</p>
<p>DESCRIBE is convenient for quick inspection, while SHOW COLUMNS offers useful filtering options and SHOW FULL COLUMNS provides additional metadata. For a complete table definition or detailed index information, use SHOW CREATE TABLE or SHOW INDEX as appropriate.</p>
<p>Regularly inspecting table structures helps you write accurate SQL queries, understand unfamiliar databases, and maintain reliable database applications.</p>
<p>To learn more about MySQL, explore the <a href="https://www.justacademy.co/course-detail/mysql-training" target="_blank" rel="noopener noreferrer">MySQL Training Course</a>. You can also <a href="https://www.justacademy.co/register-for-course-demo" target="_blank" rel="noopener noreferrer">register for a course demo</a>.</p>
</div>
