SQL with Manoj

 
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.