How to show table

☰

SQL - Select Statement :

The SQL SELECT statement is a fundamental query used to retrieve data from a database. It allows you to specify which columns you want to retrieve and which table or tables to retrieve them from. This query is essential for data retrieval and manipulation in SQL databases.

The SQL SELECT statement is the cornerstone of querying databases. It allows you to retrieve specific data from one or more tables based on specified criteria. Let's delve into the details of the SQL SELECT statement:

Syntax :

The basic syntax of the SQL SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;

SELECT: This keyword is used to specify the columns that you want to retrieve.
column1, column2, ...: These are the columns you want to select data from.
FROM: This keyword specifies the table from which you want to retrieve the data.
table_name: This is the name of the table from which you want to retrieve the data.

Example : -

Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. To retrieve the first_name and salary columns from the employees table, the SQL SELECT statement would look like this:

SELECT first_name, salary
FROM employees;

Filtering Data : -

You can also filter data using the WHERE clause in the SELECT statement. For example, to retrieve only the employees with a salary greater than 50000:

Select first_name,salary from employees where salary>50000;

Aliasing : -

You can use aliases to give columns or tables temporary names. This can make your queries more readable. For example:-

SELECT first_name AS [FirstName], salary AS [Salary]
FROM employees;

SQL Server - Listing Tables : -

we can use the "SELECT" statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database -

sys.tables
information_schema.tables
sysobjects

The SYS.TABLES View

Following is the syntax to list down all the tables in SQL using the SYS.TABLES view -

SELECT * FROM SYS.TABLES;

Following is the output of the above query -

name	object_id	principal_id	schema_id
CUSTOMERS	4195065	NULL	1
ORDER	68195293	NULL	1
COMPANY	100195407	NULL	1
TEACHERS	2107154552	NULL	1

The INFORMATION_SCHEMA.TABLES View

Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view -

SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;

Following is the output of the above query -

table_name	table_type
CUSTOMERS	BASE TABLE
ORDERS	BASE TABLE
COMPANY	BASE TABLE
TEACHERS	BASE TABLE

The SYSOBJECTS View : -

You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view -

SELECT name, id, xtype FROM sysobjects WHERE xtype = 'U';

This will produce following result :-

name	id	xtype
CUSTOMERS	4195065	U
ORDER	68195293	U
COMPANY	100195407	U
TEACHERS	2107154552	U

Different value of xtype : -

Value	Meaning
AF	Aggregate function (CLR)
C	CHECK constraint
D	Default or DEFAULT constraint
F	FOREIGN KEY constraint
L	Log
FN	Scalar function
FS	Assembly (CLR) scalar-function
FT	Assembly (CLR) table-valued function
IF	In-lined table-function
IT	Internal table
P	Stored procedure
PC	Assembly (CLR) stored-procedure
PK	PRIMARY KEY constraint (type is K)
RF	Replication filter stored procedure
S	System table
SN	Synonym
SQ	Service queue
TA	Assembly (CLR) DML trigger
TF	Table function
TR	SQL DML Trigger
TT	Table type
U	User table
UQ	UNIQUE constraint (type is K)
V	View
X	Extended stored procedure

SQL - Select Statement :

Syntax :

The basic syntax of the SQL SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;

SELECT: This keyword is used to specify the columns that you want to retrieve.
column1, column2, ...: These are the columns you want to select data from.
FROM: This keyword specifies the table from which you want to retrieve the data.
table_name: This is the name of the table from which you want to retrieve the data.

Example : -

SELECT first_name, salary
FROM employees;

Filtering Data : -

You can also filter data using the WHERE clause in the SELECT statement. For example, to retrieve only the employees with a salary greater than 50000:

Select first_name,salary from employees where salary>50000;

Aliasing : -

You can use aliases to give columns or tables temporary names. This can make your queries more readable. For example:-

SELECT first_name AS [FirstName], salary AS [Salary]
FROM employees;

SQL Server - Listing Tables : -

we can use the "SELECT" statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database -

sys.tables
information_schema.tables
sysobjects

The SYS.TABLES View

Following is the syntax to list down all the tables in SQL using the SYS.TABLES view -

SELECT * FROM SYS.TABLES;

Following is the output of the above query -

name	object_id	principal_id	schema_id
CUSTOMERS	4195065	NULL	1
ORDER	68195293	NULL	1
COMPANY	100195407	NULL	1
TEACHERS	2107154552	NULL	1

The INFORMATION_SCHEMA.TABLES View

Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view -

SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;

Following is the output of the above query -

table_name	table_type
CUSTOMERS	BASE TABLE
ORDERS	BASE TABLE
COMPANY	BASE TABLE
TEACHERS	BASE TABLE

The SYSOBJECTS View : -

SELECT name, id, xtype FROM sysobjects WHERE xtype = 'U';

This will produce following result :-

name	id	xtype
CUSTOMERS	4195065	U
ORDER	68195293	U
COMPANY	100195407	U
TEACHERS	2107154552	U

Different value of xtype : -

Value	Meaning
AF	Aggregate function (CLR)
C	CHECK constraint
D	Default or DEFAULT constraint
F	FOREIGN KEY constraint
L	Log
FN	Scalar function
FS	Assembly (CLR) scalar-function
FT	Assembly (CLR) table-valued function
IF	In-lined table-function
IT	Internal table
P	Stored procedure
PC	Assembly (CLR) stored-procedure
PK	PRIMARY KEY constraint (type is K)
RF	Replication filter stored procedure
S	System table
SN	Synonym
SQ	Service queue
TA	Assembly (CLR) DML trigger
TF	Table function
TR	SQL DML Trigger
TT	Table type
U	User table
UQ	UNIQUE constraint (type is K)
V	View
X	Extended stored procedure

Talk to us?

Post your blog

How to show table

C Basic Concept

Control statement

C Functions

C Array

C Pointers

C Dynamic Memory

C Strings

C Math

C Structure & Union

C File Handling

C Preprocessor

C Command Line

Test

SQL - Select Statement :

SQL Server - Listing Tables : -

The SYS.TABLES View

The INFORMATION_SCHEMA.TABLES View

The SYSOBJECTS View : -

SQL - Select Statement :

SQL Server - Listing Tables : -

The SYS.TABLES View

The INFORMATION_SCHEMA.TABLES View

The SYSOBJECTS View : -

Would you mind helping us.