CODEWITHSUNDEEP
sqlsql-serversql-server-2012
sobhan
sobhan

Reputation: 13

how to have list of all tables in a database with dbo schema?

i do this with filter table in object explorer but i want do this with query and when i execute the query,returns all tables with dbo schema and their fields.

Upvotes: 1

Views: 12229

Answers (3)

A_Sk
A_Sk

Reputation: 4630

To get All the Table, Schema, Columns and data Type in database use below query.

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS

Upvotes: 0

Ven
Ven

Reputation: 2014

Returns from sys.tables , use schema_id to filter 

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS TableName
FROM sys.tables where SCHEMA_NAME(schema_id) = 'dbo'

To Include Views in Schema

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS TableName
FROM sys.tables where SCHEMA_NAME(schema_id) = 'dbo'

UNION 

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS TableName
FROM sys.views where SCHEMA_NAME(schema_id) = 'dbo'

Upvotes: 4

Madhukar
Madhukar

Reputation: 1242

Are you trying to get this information?

SELECT * FROM sys.tables WHERE schema_id = 1
--SchemaId 1 is for 'dbo' schema

If you want columns as well, then try this.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo'

Upvotes: 3

Related Questions