How to get all table names of a particular database by using a query in SQL Server

Tutorialsrack 30/09/2019 SQL Server

In this article, we will learn how to how to get all table names of a particular database by using a query in SQL Server.

There are various ways you can find all table names present in a database by using the following system or metadata tables in SQL server:

By using sys.sysobjects

For an older version of SQL Server like SQL Server 2000, we can query system table sys.sysobjects that contains one row for each object that is created within a database.

USE Your_DatabaseName;

SELECT name as Table_Name
FROM sys.sysobjects
WHERE xtype = 'U';

By using INFORMATION_SCHEMA.TABLES

Another way to get all the table name of a specific database by using the system view INFORMATION_SCHEMA.TABLES. This returns one row for each table or view in the current database for which the current user has permissions.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Your_DatabaseName';

By using Sys.objects

One another way to get a list of all the tables by using a system view sys.objects. Sys.objects contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

USE Your_DatabaseName;

SELECT name as Table_Name
FROM sys.objects
WHERE type = 'U';

By using Sys.tables

One another way to get a list of all the tables by using a system view sys.tables. Sys.tables returns a row for each user table in SQL Server.

USE Your_DatabaseName;

SELECT name as Table_Name
FROM sys.Tables
WHERE type = 'U';

By using sp_msForEachTable

One another way to get a list of all the tables is to use undocumented stored procedure sp_MSforeachtable that is mostly used to apply a T-SQL command to every table, that exists in the current database.

EXEC sp_msforeachtable 'print ''?''';

I hope this article will help you to understand how to get all table names of a particular database by using a query in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts