Some time back I wrote an article about getting the list of all the columns in a table. Today we will see how to get the list of user tables in a database. There are several ways to get the list of all tables in a database in SQL Server. Here we will see two quick methods using TSQL metadata catalogs SYS.TABLES and INFORMATION_SCHEMA.TABLES.
Sys.tables returns all the user tables in a database. Here is how to use it to list down all the tables.
USE WideWorldImporters GO SELECT * FROM SYS.TABLES GO
While using INFORMATION_SCHEMA.TABLES, you have to filter the result for TABLE_TYPE = ‘BASE TABLE’ as this metadata catalog lists table views. Here is an example.
USE WideWorldImporters GO SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' GO
- Read more about sys.tables at Microsoft Docs.