Advertisements
Home > SQL Trivia > SQL Trivia – Multiple ways to get list of all tables in a database

SQL Trivia – Multiple ways to get list of all tables in a database


 
This is one of a typical Interview Question you might face while going for an opening of a SQL Developer. This looks very simple question but its also an important question to judge a person if he/she knows SQL basics and has really worked in SQL Server or not.

–> So, here are following ways you can query the SQL Server metadata and system tables to get list of all tables in a particular database, by using:

1. sys.tables

select * from sys.tables

2. sys.objects with filter of type = ‘U’

select * from sys.objects where type = 'U'

3. sys.sysobjects with filter of type = ‘U’

select * from sys.sysobjects where type = 'U'

4. INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'TABLE'

5. sp_msforeachtable undocumented function

exec sp_msforeachtable 'print ''?'''

 

Out of all these the best option is to use the 4th one, i.e. INFORMATION_SCHEMA.TABLES views, as Microsoft itself suggest to use it instead of first three & the 5th option.


Advertisements
Categories: SQL Trivia
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: