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:
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';
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';
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';
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';
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!
Comments