There are different ways of listing user tables within a database. The first method is with the use of the sp_tables system stored procedure:
EXECUTE sp_tables @Table_Type='''TABLE'''
The sp_tables system stored procedure returns a list of objects that can be queried in the current environment. The sp_tables system stored procedure returns user tables, views and system tables. To return only user tables, the @Table_Type parameter needs to be passed a value of 'TABLE'.
Another way of getting a list of user tables is with the INFORMATION_SCHEMA.TABLES system view. The INFORMATION_SCHEMA.TABLES system view returns one row for each table in the current database for which the current user has permissions.
SELECT [TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_TYPE] = 'BASE TABLE'
The INFORMATION_SCHEMA.TABLES system view returns both views and user tables. To just return user tables, the TABLE_TYPE needs to be checked for a value of ‘BASE TABLE’.
The third way of getting a list of user tables is by querying the different system views, namely, the sys.tables, sys.all_objects, sys.objects and dbo.sysobjects.
SELECT [Name] FROM [sys].[tables]
SELECT [Name] FROM [sys].[all_objects] WHERE [type] = 'U'
SELECT [Name] FROM [sys].[objects] WHERE [type] = 'U'
SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = 'U'