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.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.