Use SQL statements to query all tables in the database

I believe many of my friends have the need to use SQL statements to find all the tables in the specified database. For example, this requirement is even more common for database-related tools and projects, this chapter summarizes several methods to query all tables in a database using SQL statements.

1. Use the sysobjects system table to create a corresponding row for each object (such as constraints, default values, logs, rules, and stored procedures) in the database, in this table, all records whose xtype is u are filtered out, which is the table in the database. The example statement is as follows ::

Select * From sysobjects where xtype = 'U'

Note: In SQL server2005, sys. the objects directory view replaces the sysobjects system table. In SQL server2005 and later versions, we can use the sysobjects system table and SYS. any object in the objects directory view is used to query all tables.

2. Use the SYS. Tables directory view SYS. Tables directory view to return a row for each table object. The example statement is as follows:

Select * From SYS. Tables

Note: The sys. Tables directory view can only be used in SQL server2005 or later versions.

3. Use the sp_tables stored procedure to return a list of objects that can be queried in the current environment. This represents any object that can appear in the from clause. Run the following statement:

Exec sp_tables

In the result set, all the records whose table_type is equal to table are filtered out.

Summary: there are many ways to find all the tables in the database. The small Editor only lists the three commonly used tables, hoping to help you.