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

Editorial / Best Answer

santanukd  

  • Member Since May-2008 | 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 - 42 of 42 Answers

narendra

  • Nov 25th, 2006
 

SELECT * FROM TAB:

  Was this answer useful?  Yes

Sanjay Rathore

  • Dec 1st, 2006
 

Select * from tab;

this will work in ORACLE

For SQL Server use:

SELECT * FROM sysobjects where xtype ='U'

Mark

  • Dec 2nd, 2006
 

For 2005 it also can be:

Select * from sys.tables

Ramesh R. Jaiswar

  • Jan 18th, 2007
 

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

     sp_help

Regards

Chitra.M

  • Mar 9th, 2007
 

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

purnaprasad

  • Mar 17th, 2007
 

hi all
pl use this to retrive the tables

select * from information_schema.tables & then press F5----to retrive Tables

select * from information_schema.views & then press F5----to retrive Views

  Was this answer useful?  Yes

raajbir singh

  • 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

  Was this answer useful?  Yes

Prabhakaran

  • Sep 27th, 2007
 

sp_tables  (for Microsoft sql server)


select *from tab;   ( for sql plus)

  Was this answer useful?  Yes

Dheeraj Chakravarthy

  • Oct 10th, 2007
 

select * from information_schema.tables

- Dheeraj

  Was this answer useful?  Yes

santanukd

  • 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]

  Was this answer useful?  Yes

saikiran

  • Jul 22nd, 2011
 

select * from sys.tables

  Was this answer useful?  Yes

Keer

  • Apr 25th, 2012
 

select * from sys.tables where type = U

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions