How to list all tables with their schemas in a database?

Question

Post reply

How to list all tables with their schemas in a database?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
February 4, 2013 at 1:06 pm

#269765

It is for SQL 2008.
Many thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's all in sys.tables, but i prefer to use built in functions instead of joining sys.schemas:

SELECT

SCHEMA_NAME(schema_id) As SchemaName ,

name As TableName

from sys.tables

ORDER BY name

--alternate:

SELECT

sch.name As SchemaName ,

tbl.name As TableName

from sys.tables tbl

inner join sys.schemas sch on tbl.schema_id = sch.schema_id

ORDER BY tbl.name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I typically just go for sys.objects like the following.

SELECT SCHEMA_NAME(schema_id) As SchemaName

,name As TableName

FROM sys.objects

WHERE type = 'U'

An interesting sidebar on this is that if you look at the execution plan, the version going to sys.tables will get it's data from sysschobjs and sysidxstats and will cost a little bit more than just going direct to sys.objects (for which sysschobjs is the base object).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

You can also use the following:

SELECT Table_Schema, Table_Name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'