How to Show Tables in SQL Server Database
Database developers and administrators have to monitor the condition of databases, inclusive of all associated objects and processes. Often, routine tasks such as database design, performance tuning, and security maintenance require the ability to view the list of tables within a particular database. Hence, database professionals search for reliable and efficient ways to accomplish this task accurately.
All database systems offer methods to list database tables. SQL Server, unlike MySQL/MariaDB, does not directly support the SHOW TABLES function, but it offers a range of alternative ways to fulfill the same requirement. In this article, we examine the diverse methods of listing tables in SQL Server.
Depending on the SQL Server version, these methods may slightly vary. Therefore, we'll delve into the syntax, scope, and respective advantages and disadvantages of each method.
The role of SQL Server system catalog views
System catalog views are the default interface in SQL Server that provides access to the information about SQL Server metadata, both schema objects (such as tables, views, procedures) and instance objects (such as logins, settings, access privileges). Users can query catalog views similarly to querying any other database tables or views, but direct modifications of catalog views are not available.
Introduced in SQL Server 2005, catalog views provide the most consistent information, regardless of changes in the system base tables. Furthermore, querying the system catalog view returns descriptive table column names, which is particularly helpful for newer users. Consequently, Microsoft recommends the usage of catalog views whenever accessing SQL Server metadata is necessary.
The catalog views hierarchy is structured in two "tiers." The upper tier comprises sys.objects — the view containing comprehensive information about all schema-scoped objects in a particular database. The lower tier includes views like sys.tables, sys.views, and sys.procedures, along with other custom database-specific views. It's important to note that these lower-tier views inherit all columns from sys.objects, in addition to their unique columns.
As these system catalog views provide information about all schema objects, they can list the database tables for users in SQL Server provided that they have the sys.tables view permissions. The database owners have them by default, other users need such permissions assigned to them.
Use sys.tables to list tables
The sys.tables system catalog view is the default method for listing tables in SQL Server databases — it provides a comprehensive list of all user tables stored in the current database (you can switch to another database by specifying its name in the query).
SELECT
name
FROM sys.tables;
Note
While sys.tables is the most convenient option, you may require the appropriate privileges to view sys.tables if you are not the database owner. Additionally, it's important to keep in mind that querying sys.tables is specific to SQL Server and is not supported by other relational database management systems (RDBMSs).
Use sys.objects to list tables
As mentioned previously, sys.objects is the primary system catalog view in SQL Server where you can access the information about all schema objects, not limited to tables alone. If you specifically require a list of particular objects, such as tables, you can apply a filter using the WHERE clause:
SELECT
name
FROM sys.objects
WHERE type = 'U';
In this query, U stands for the user table. The output is:
While sys.objects is a valuable resource for retrieving a comprehensive list of database objects, including tables. However, if your task exclusively involves tables, you should always modify the command as demonstrated above (or refer to sys.tables).
Use INFORMATION_SCHEMA.TABLES to list tables
INFORMATION_SCHEMA.TABLES is a commonly used method to retrieve a list of tables from the current database. The syntax to return all tables and views with one query is as follows:
SELECT
*
FROM INFORMATION_SCHEMA.TABLES;
If you require a list of actual tables excluding views, you can modify the command as follows:
SELECT
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
Note
The usage of INFORMATION_SCHEMA.TABLES is applicable to newer SQL Server versions (2005 and above), as older versions do not support this method. Additionally, querying this view tends to be slower compared to the system catalogs mentioned earlier. Therefore, it may not be the optimal and most efficient approach when working with large databases.
Use dbo.sysobjects to list tables
dbo.sysobjects is an outdated version of the sys.objects view that was in use prior to the release of SQL Server 2005. If you are working with SQL Server 2000 or an earlier version and need to list tables in your databases. You should utilize dbo.sysobjects.
It provides the same results as sys.objects: the list of all objects in the database, including tables. When you need it listing the database tables exclusively, you can modify the query in the same manner as in the modern version by adding the WHERE clause to filter object types:
SELECT
name
FROM dbo.sysobjects
WHERE xtype = 'U';
Note
This command has been deprecated in SQL Server 2005. It is still active in higher versions of SQL Server, but Microsoft recommends using more up-to-date options like querying sys.tables or sys.objects.
Use the sp_tables stored procedure to list tables
The stored procedure sp_tables can be used to fetch a list of database objects, excluding synonym objects. The simplest way to query this stored procedure is:
sp_tables;
However, this approach may not be the most convenient option as it retrieves the entire list of database objects. For more precise results, you can modify the command to filter the output based on the table type, table name, table owner, and table qualifier.
EXEC sp_tables @table_owner = 'dbo'
,@table_qualifier = 'sakila'
,@table_type = "'TABLE'";
While the sp_tables stored procedure can be useful, many SQL Server experts find it more limited and complex to use in larger queries compared to querying sys.tables and sys.objects.
Show all tables in SQL Server database with dbForge Studio
for SQL Server
Database specialists often use T-SQL commands as their primary option. An alternative approach is to utilize GUI tools like Microsoft's SQL Server Management Studio (SSMS) or dbForge Studio for SQL Server, which we used while preparing this material. These GUI tools offer a more user-friendly interface and allow users to perform actions with a simple click instead of writing scripts.
To view tables in dbForge Studio for SQL Server, you can use Database Explorer. Follow these steps:
- Select the desired database in the left pane.
- Expand the selected database to reveal its contents.
- Expand the Tables folder to display all tables within the database. You can explore any specific table further to access information about its columns, triggers, indexes, constraints, and more.
The Database Explorer not only enables you to view table details but also provides the ability to edit tables directly. To do this, simply right-click on the desired table and choose the appropriate option from the menu.
Conclusion
Both T-SQL commands and GUI tools are widely used by database experts. GUI tools are particularly beneficial for beginners and non-technical users, but they are also favored by database experts due to their ability to accelerate tasks and reduce effort. In practice, you will most likely see the combination of T-SQL and GUI used by the same specialists when appropriate.
dbForge Studio for SQL Server stands out as an all-in-one solution for performing various tasks related to managing, designing, developing, and administering databases in SQL Server. It offers comprehensive functionality and can be experienced to its fullest extent through a fully functional free trial. Feel free to download the tool give it a try.
