How to View Table in SQL: A Complete Guide for Beginners and Beyond
Viewing a table in SQL is one of the first skills every database learner needs to master. Worth adding: whether you want to inspect the structure of a table, browse its stored data, or confirm that a table exists before running a query, knowing how to view table in SQL saves you time and prevents costly mistakes. This guide walks you through every practical method across the most popular database systems, from simple commands to more advanced inspection techniques, so you can confidently explore your data no matter which platform you use.
Introduction: Why Viewing Tables Matters
A database table is the fundamental building block of any relational database. Day to day, it stores information in rows and columns, much like a spreadsheet. Before you write complex queries, join multiple tables, or delete records, you almost always need to view the table to understand what you are working with Not complicated — just consistent..
Honestly, this part trips people up more than it should Not complicated — just consistent..
There are two common meanings behind the phrase "view a table":
- Viewing the structure of a table, meaning its columns, data types, keys, and constraints.
- Viewing the data stored inside the table, meaning the actual rows and values.
Both are essential, and this article covers each one in detail. Understanding your tables thoroughly leads to cleaner queries, fewer errors, and better overall database management Most people skip this — try not to. Turns out it matters..
Understanding the Two Ways to View a Table
Before diving into commands, it helps to separate the two goals clearly.
Viewing Table Structure (Schema)
The schema describes the shape of your table. It tells you which columns exist, what type of data each column holds (such as INT, VARCHAR, or DATE), and whether there are primary keys or constraints. This is often called describing a table or inspecting the schema.
Viewing Table Data (Rows)
Viewing the data means retrieving the actual records stored in the table. This is done with the SELECT statement, which is the most widely used command in SQL for reading information Nothing fancy..
How to View Table Structure in SQL
Different database systems use slightly different commands, so let's look at the most common ones.
Using DESCRIBE in MySQL and MariaDB
In MySQL, the quickest way to see a table's structure is the DESCRIBE command:
DESCRIBE employees;
You can also use the shorthand DESC:
DESC employees;
This returns a result showing each column name, its data type, whether it allows NULL, and any keys or default values.
Using SHOW COLUMNS in MySQL
An alternative that gives similar information is:
SHOW COLUMNS FROM employees;
To see the full creation statement, including constraints and indexes, use:
SHOW CREATE TABLE employees;
Using INFORMATION_SCHEMA (Universal Approach)
Almost every major relational database supports the INFORMATION_SCHEMA, a set of read-only views that describe all other objects in the database. This is the most portable method because it works across MySQL, PostgreSQL, SQL Server, and more Still holds up..
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'employees';
This query returns the column names, their data types, and whether they accept NULL values Worth keeping that in mind..
Viewing Table Structure in PostgreSQL
PostgreSQL uses a slightly different syntax. To describe a table, you can use the psql meta-command:
\d employees
Or, for a more detailed view:
\d+ employees
Within standard SQL, you can also query the information_schema just like in MySQL.
Viewing Table Structure in SQL Server
In Microsoft SQL Server, you can use the system stored procedure:
EXEC sp_help 'employees';
Alternatively, you can query the INFORMATION_SCHEMA:
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'employees';
Viewing Table Structure in Oracle
Oracle provides the DESCRIBE command inside SQL*Plus or SQL Developer:
DESCRIBE employees;
You can also query the ALL_TAB_COLUMNS or USER_TAB_COLUMNS views for detailed schema information.
How to View Table Data in SQL
Once you understand the structure, the next step is to look at the actual records. The SELECT statement is your primary tool Simple as that..
The Basic SELECT Statement
To view all data from a table, use:
SELECT * FROM employees;
The asterisk (*) means "all columns." While convenient, it is often better practice to select only the columns you need, especially in large tables:
SELECT first_name, last_name, hire_date
FROM employees;
Limiting the Number of Rows
When a table contains thousands or millions of rows, viewing everything at once is impractical. Use a limit clause to preview just a few rows.
In MySQL and PostgreSQL:
SELECT * FROM employees
LIMIT 10;
In SQL Server, the syntax uses TOP:
SELECT TOP 10 * FROM employees;
In Oracle, you can use the FETCH FIRST clause:
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;
Filtering Rows with WHERE
To view specific records that match a condition, add a WHERE clause:
SELECT * FROM employees
WHERE department = 'Sales';
This returns only the rows where the department equals Sales.
Sorting the Results
To view data in a particular order, use ORDER BY:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
This displays employees sorted from the highest salary to the lowest.
How to View a List of All Tables in a Database
Sometimes you do not yet know which tables exist. Before you can view a table, you need to find its name.
In MySQL
SHOW TABLES;
In PostgreSQL
\dt
Or via SQL:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
In SQL Server
SELECT name FROM sys.tables;
Or:
EXEC sp_tables;
In Oracle
SELECT table_name FROM user_tables;
Scientific Explanation: How SQL Retrieves Table Information
Behind the scenes, when you run a command like DESCRIBE or query INFORMATION_SCHEMA, the database engine reads its own metadata, which is data about data. Every relational database stores a system catalog (also called a data dictionary) that records the structure of every table, column, index, and constraint.
If you're execute SELECT * FROM employees, the query optimizer parses your statement, checks the catalog to locate the table, retrieves the relevant data pages from storage, and returns the result set to your client. The INFORMATION_SCHEMA is simply a standardized, read-only presentation layer over this internal catalog, which is why the same query works across so many different database products.
Understanding this distinction helps you appreciate why viewing structure and viewing data are separate operations: one reads metadata from the catalog, while the other reads actual records from storage It's one of those things that adds up..
Practical Tips for Viewing Tables Efficiently
- Always preview before selecting everything. Use
LIMITorTOPto avoid overwhelming your screen or client with massive result sets. - Select specific columns instead of using
*when you only need a few fields. This improves performance and readability. - Use
INFORMATION_SCHEMAwhen you need queries that work across multiple database systems. - Confirm a table exists before running operations that modify data, especially
UPDATEor `