devxlogo

Get All Tables and Views Inside a Database

Get All Tables and Views Inside a Database

A nifty little trick is to be able to list all the Tables or Views, or even both, present in a database. A quick way to get All the Tables and Views follows:

SELECT * FROM information_schema.tablesWHERE Table_schema = 'dbo'ORDER BY Table_name

In the above query, I retrieved all table-like objects based on the dbo schema. This produces a list of all views and tables.

This:

SELECT TOP 1000 [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[VIEW_DEFINITION],[CHECK_OPTION],[IS_UPDATABLE]FROM [lms_db].[INFORMATION_SCHEMA].[VIEWS] 

Gets all Views only, whereas this:

SELECT * FROM information_schema.tablesWHERE Table_schema = 'dbo' and Table_type = 'BASE TABLE'ORDER BY Table_name

Is a bit more narrowed down to get the tables only.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist