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 -


nameobject_idprincipal_idschema_id
CUSTOMERS4195065NULL1
ORDER68195293NULL1
COMPANY100195407NULL1
TEACHERS2107154552NULL1

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_nametable_type
CUSTOMERSBASE TABLE
ORDERSBASE TABLE
COMPANYBASE TABLE
TEACHERSBASE 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 :-

nameidxtype
CUSTOMERS4195065U
ORDER68195293U
COMPANY100195407U
TEACHERS2107154552U

Different value of  xtype  : -

ValueMeaning
AFAggregate function (CLR)
CCHECK constraint
DDefault or DEFAULT constraint
FFOREIGN KEY constraint
LLog
FNScalar function
FSAssembly (CLR) scalar-function
FTAssembly (CLR) table-valued function
IFIn-lined table-function
ITInternal table
PStored procedure
PCAssembly (CLR) stored-procedure
PKPRIMARY KEY constraint (type is K)
RFReplication filter stored procedure
SSystem table
SNSynonym
SQService queue
TAAssembly (CLR) DML trigger
TFTable function
TRSQL DML Trigger
TTTable type
UUser table
UQUNIQUE constraint (type is K)
VView
XExtended stored procedure

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 -


nameobject_idprincipal_idschema_id
CUSTOMERS4195065NULL1
ORDER68195293NULL1
COMPANY100195407NULL1
TEACHERS2107154552NULL1

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_nametable_type
CUSTOMERSBASE TABLE
ORDERSBASE TABLE
COMPANYBASE TABLE
TEACHERSBASE 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 :-

nameidxtype
CUSTOMERS4195065U
ORDER68195293U
COMPANY100195407U
TEACHERS2107154552U

Different value of  xtype  : -

ValueMeaning
AFAggregate function (CLR)
CCHECK constraint
DDefault or DEFAULT constraint
FFOREIGN KEY constraint
LLog
FNScalar function
FSAssembly (CLR) scalar-function
FTAssembly (CLR) table-valued function
IFIn-lined table-function
ITInternal table
PStored procedure
PCAssembly (CLR) stored-procedure
PKPRIMARY KEY constraint (type is K)
RFReplication filter stored procedure
SSystem table
SNSynonym
SQService queue
TAAssembly (CLR) DML trigger
TFTable function
TRSQL DML Trigger
TTTable type
UUser table
UQUNIQUE constraint (type is K)
VView
XExtended stored procedure

Talk to us?

Post your blog