GeekInterview.com
Series: Subject: Topic:
Question: 139 of 198

what is query of display the all tables in SQL server ?

Asked by: Interview Candidate | Asked on: Nov 24th, 2006

Editorial / Best Answer

Answered by: santanukd

View all answers by santanukd

Member Since May-2008 | Answered On : May 14th, 2009

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.


Select OBJECT_TYPE,OBJECT_NAME
FROM
(
Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
Union
Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
Union
Select 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES
) R

Where R.OBJECT_SCHEMA=[your-schema-name-here]

Showing Answers 1 - 16 of 16 Answers
narendra

Answered On : Nov 25th, 2006

SELECT * FROM TAB:

  
Login to rate this answer.
Sanjay Rathore

Answered On : Dec 1st, 2006

Select * from tab;this will work in ORACLEFor SQL Server use:SELECT * FROM sysobjects where xtype ='U'

Yes  1 User has rated as useful.
  
Login to rate this answer.
Mark

Answered On : Dec 2nd, 2006

For 2005 it also can be:Select * from sys.tables

Yes  2 Users have rated as useful.
  
Login to rate this answer.
ualike

Answered On : Dec 5th, 2006

View all answers by ualike

Thanks

Yes  1 User has rated as useful.
  
Login to rate this answer.
Ramesh R. Jaiswar

Answered On : Jan 18th, 2007

To display all table in sql thorugh  T-Sql  type below given command.     sp_helpRegards

Yes  2 Users have rated as useful.
  
Login to rate this answer.
Chitra.M

Answered On : Mar 9th, 2007

Sp_tables is used in T-SQL for getting all the tables in respective databases

Yes  1 User has rated as useful.
  
Login to rate this answer.
purnaprasad

Answered On : Mar 17th, 2007

Hi allpl use this to retrive the tablesselect * from information_schema.tables & then press F5----to retrive Tablesselect * from information_schema.views & then press F5----to retrive Views

  
Login to rate this answer.
raajbir singh

Answered On : Jun 25th, 2007

See sp_help will list up all the views along with the tables in the database.The question is to have the table only.rather this select * from sysobjects where type like '%u' order by name Query may help

  
Login to rate this answer.
mydeen83

Answered On : Aug 24th, 2007

View all answers by mydeen83

Sp_help  --> For display all tables in the database

  
Login to rate this answer.
Prabhakaran

Answered On : Sep 27th, 2007

Sp_tables  (for Microsoft sql server)select *from tab;   ( for sql plus)

  
Login to rate this answer.
Dheeraj Chakravarthy

Answered On : Oct 10th, 2007

Select * from information_schema.tables- Dheeraj

  
Login to rate this answer.
santanukd

Answered On : May 14th, 2009

View all answers by santanukd

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there. Select OBJECT_TYPE,OBJECT_NAME FROM(Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWSUnionSelect 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWSUnionSelect 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTSUnionSelect 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES) RWhere R.OBJECT_SCHEMA=[your-schema-name-here]

  
Login to rate this answer.
satheyaraaj

Answered On : Jul 9th, 2011

View all answers by satheyaraaj

Database Name..Sp_Help

  
Login to rate this answer.

SELECT * FROM SYS.TABLES

SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U'

SELECT * FROM SYS.OBJECTS WHERE TYPE = 'U'

  
Login to rate this answer.
saikiran

Answered On : Jul 22nd, 2011

Select * from sys.tables

  
Login to rate this answer.
Keer

Answered On : Apr 25th, 2012

Select * from sys.tables where type = U

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.