Get list of tables in SQL Server

How to get tables list in SQL Server

 As a DBA, you must find the MS SQL Server tables list for the development team's needs. We will describe a slightly different approach to querying and returning a list of all user-created tables. The SYS.SYSOBJECTS metadata view must be queried. Every object that has been created in the database, including stored procedures, views, and user tables, has a row in the table SYS.SYSOBJECTS.

 

SELECT

  *

FROM

  SYSOBJECTS

WHERE

  xtype = 'U';

GO

 

Since it must store information about nearly everything added to the server over time, the SYSOBJECTS table contains a few dozen columns of data. Hence, in order to identify a list of user-created tables (while disregarding system tables), we must look for results where the value U, which denotes a user table, is present in the xtype column, which indicates the object type for that row. The final TSQL statement should resemble the following:

 

SELECT

  *

FROM

  SYSOBJECTS

WHERE

  xtype = 'U';

GO

 

When using a newer version of SQL Server (SQL 2005 or greater), listing all the tables is as simple as querying the INFORMATION SCHEMA views that are automatically included in SQL Server. You can quickly access a range of metadata for this specific SQL Server instance using these, including details about COLUMNS, ROUTINES, and even TABLES.

 

SELECT

  *

FROM

  INFORMATION_SCHEMA.TABLES;

GO

 

By utilising the INFORMATION SCHEMA, four columns could be returned as you can see. TABLES view, but the TABLE TYPE column, which identifies whether the table in that row is a real table (BASE TABLE) or a view, is the most crucial information (VIEW).

 

SELECT

  *

FROM

 INFORMATION_SCHEMA.TABLES

WHERE

  TABLE_TYPE = 'BASE TABLE';

GO