List names of all tables in a SQL Server 2012 schema
SqlSql ServerSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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';
Solution 3 - Sql
SQL Server 2005, 2008, 2012 or 2014:
SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = 'dbo'
For more details: https://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-tables-in-a-database-using-tsql
Solution 4 - Sql
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
Solution 5 - Sql
SELECT *
FROM sys.tables t
INNER JOIN sys.objects o on o.object_id = t.object_id
WHERE o.is_ms_shipped = 0;
Solution 6 - Sql
select * from [schema_name].sys.tables
This should work. Make sure you are on the server which consists of your "[schema_name]"
Solution 7 - Sql
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