When working with SQL Server databases, it is common to need information about the tables within a database. Many people use the script SELECT * FROM sys.tables to retrieve this information. While this script provides various details about the tables, it may not be efficient if you only need the table names.

A more efficient way to retrieve the table names is by using the stored procedure sys.sp_tables. This stored procedure returns all the tables in the database, along with their schema names and qualifiers. It also includes system catalog views and other views in the result set.

Let’s compare the performance of these two methods using the AdventureWorks database as an example:

Method 1: Using sys.tables

SET STATISTICS IO ON
USE AdventureWorks
GO

SET STATISTICS IO ON
SELECT * FROM sys.tables
GO

This method returns 81 rows and provides detailed information about each table. However, the query execution plan shows a higher cost for this query batch.

Method 2: Using sys.sp_tables

EXEC sys.sp_tables
GO

This method also returns 81 rows, but it performs better than the previous method. The query execution plan shows a lower cost for this query batch.

If you only need to know the names of the tables, I recommend using sys.sp_tables as it requires less typing and provides better performance.

By optimizing your table queries, you can improve the efficiency of your SQL Server database operations. Consider using the appropriate method based on your specific requirements to ensure optimal performance.