Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Aug 2, 2016

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.tables
WHERE 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.tables
WHERE Table_schema = 'dbo' and Table_type = 'BASE TABLE'
ORDER BY Table_name

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

Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date