MysqlHow-ToBeginner · 3 min read

How to Check View Definition in MySQL Quickly

To check a view's definition in MySQL, use the SHOW CREATE VIEW view_name; command which shows the SQL used to create the view. Alternatively, query the INFORMATION_SCHEMA.VIEWS table to see the VIEW_DEFINITION column for the view's SQL code.

📐

Syntax

The main ways to check a view's definition in MySQL are:

SHOW CREATE VIEW view_name; - Displays the full SQL statement used to create the view.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'view_name' AND TABLE_SCHEMA = 'database_name'; - Retrieves the SQL code from the system metadata.

Replace view_name with your view's name and database_name with your database name.

sql

SHOW CREATE VIEW view_name;

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view_name' AND TABLE_SCHEMA = 'database_name';

💻

Example

This example shows how to create a simple view and then check its definition using both methods.

sql

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Alice', 5000.00), (2, 'Bob', 6000.00);

CREATE VIEW high_salary AS
SELECT name, salary FROM employees WHERE salary > 5500;

-- Check view definition with SHOW CREATE VIEW
SHOW CREATE VIEW high_salary;

-- Check view definition from INFORMATION_SCHEMA
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'high_salary' AND TABLE_SCHEMA = 'testdb';

Output

View Create View high_salary CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `high_salary` AS select `name`, `salary` from `employees` where (`salary` > 5500) VIEW_DEFINITION select `name`, `salary` from `employees` where (`salary` > 5500)

⚠️

Common Pitfalls

Common mistakes when checking view definitions include:

Using the wrong database name in the INFORMATION_SCHEMA.VIEWS query, which returns no results.
Trying to use SHOW CREATE VIEW without proper privileges, causing permission errors.
Expecting the VIEW_DEFINITION column to include the full CREATE VIEW statement; it only contains the SELECT query part.

sql

/* Wrong database name example - returns empty result */
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'high_salary' AND TABLE_SCHEMA = 'wrongdb';

/* Correct usage */
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'high_salary' AND TABLE_SCHEMA = 'testdb';

📊

Quick Reference

Command	Description
SHOW CREATE VIEW view_name;	Shows full SQL used to create the view.
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'view_name' AND TABLE_SCHEMA = 'database_name';	Shows the SELECT query of the view.

✅

Key Takeaways

Use SHOW CREATE VIEW view_name; to see the full SQL that created the view.

Query INFORMATION_SCHEMA.VIEWS for VIEW_DEFINITION to get the view's SELECT statement.

Always specify the correct database name when querying INFORMATION_SCHEMA.

SHOW CREATE VIEW requires proper privileges to run successfully.

VIEW_DEFINITION shows only the SELECT part, not the full CREATE VIEW syntax.