Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Get a List of User Tables Within a Database
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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'

Back to Tip of the Day List Next Tip