SQL Server - List All Tables - How to Show All Tables - Table List

You could Use any of the following SQL's


To return all tables and views in one query, execute the following TSQL statement:

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

If you only wish to retrieve actual tables and filter out views from the results, add a WHERE TABLE_TYPE = 'BASE TABLE' clause:

SELECT *
FROM databaseName.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

-------------------------------------Or---------------------------------------------------

The tableSYSOBJECTS houses a couple dozen columns of data since it must hold information about virtually everything added to the server over time. o find a list of user-created tables (thus ignoring system tables), we’ll need to find results where the xtype column (which specifies the object type for that row) is equal to the value U, which stands for user table.

SELECT *
FROM SYSOBJECTS
WHERE xtype = 'U';

Below is a list of other object types you can search for as well:

• 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

------------------------------------Or----------------------------------------------------

EXEC sp_msforeachtable 'print ''?'''