CODEWITHSUNDEEP
sqlsql-server
AnkitGarg43
AnkitGarg43

Reputation: 723

List names of all tables in a SQL Server 2012 schema

I have a schema in SQL Server 2012.

Is there a command that I can run in SQL to get the names of all the tables in that schema that were populated by user?

I know a similar query for MySQL SHOW TABLES; but this does not work with SQL Server.

Upvotes: 61

Views: 293083

Answers (7)

nzrytmn
nzrytmn

Reputation: 6911

When you want just the name of the table the easiest way is I guess just doing this:

SELECT * FROM INFORMATION_SCHEMA.TABLES

when need like full name with schema and table name , than just do like this:

SELECT TABLE_SCHEMA + '.' + TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES

Upvotes: 2

Saman Kapali
Saman Kapali

Reputation: 27

select * from [schema_name].sys.tables

This should work. Make sure you are on the server which consists of your "[schema_name]"

Upvotes: 0

Lorena Pita
Lorena Pita

Reputation: 1516

SELECT t1.name AS [Schema], t2.name AS [Table]
FROM sys.schemas t1
INNER JOIN sys.tables t2
ON t2.schema_id = t1.schema_id
ORDER BY t1.name,t2.name

Upvotes: 6

Nando
Nando

Reputation: 388

SQL Server 2005, 2008, 2012 or 2014:

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = 'dbo'

For more details: How do I get list of all tables in a database using TSQL?

Upvotes: 15

Kev
Kev

Reputation: 119806

Your should really use the INFORMATION_SCHEMA views in your database:

USE <your_database_name>
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES

You can then filter that by table schema and/or table type, e.g.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Upvotes: 88

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'schema_name';

Upvotes: 47

jtimperley
jtimperley

Reputation: 2544

SELECT *
FROM sys.tables t
INNER JOIN sys.objects o on o.object_id = t.object_id
WHERE o.is_ms_shipped = 0;

Upvotes: 2

Related Questions