51

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.

acdcjunior
  • 114,460
  • 30
  • 289
  • 276
AnkitGarg43
  • 623
  • 1
  • 5
  • 7
  • What does "*populated by user*" mean? Its very ambiguous in this context. – RBarryYoung Jan 23 '14 at 15:44
  • possible duplicate of [SQL statement to grab table names, views, and stored procs, order by schema](http://stackoverflow.com/questions/2516173/sql-statement-to-grab-table-names-views-and-stored-procs-order-by-schema) – Charles Burns Jan 23 '14 at 15:52
  • What I meant by that was, some tables are created by the system and some are created by the code written and executed by the program for which the user is using the database. Sorry for being ambiguous. But I found the solution for my problem. – AnkitGarg43 Jan 23 '14 at 15:52

6 Answers6

76

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'
Kev
  • 112,868
  • 50
  • 288
  • 373
  • 3
    Meh, while standard and portable, I recommend against INFORMATION_SCHEMA, mostly because they're incomplete: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx – Aaron Bertrand Jan 23 '14 at 15:30
  • @AaronBertrand - you have a point, I use them up to the point I can't, then spelunk the system tables. – Kev Jan 23 '14 at 15:34
  • see and I don't see the point of that. Why use something when inevitably you'll need to switch gears? If you start with the catalog views, you don't have to change tactics when you come across something that INFORMATION_SCHEMA doesn't bother to cover. Kind of like having a cheese allergy, but eating cheese until it makes you sick. I'd rather just pretend that cheese (and INFORMATION_SCHEMA) doesn't exist. – Aaron Bertrand Jan 23 '14 at 15:35
40
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';
Aaron Bertrand
  • 242,666
  • 35
  • 420
  • 451
  • This does not limit to tables 'populated by user'. – jtimperley Jan 23 '14 at 15:27
  • @jtimperley Well, I assumed "populated by user" was in reference to tables created by a user using a specific schema. In any case, what system tables do you see returned by this query? If you enable diagram support, `is_ms_shipped` is 0 for `sysdiagrams`, so your filter doesn't exclude it either. – Aaron Bertrand Jan 23 '14 at 15:27
  • (Not that `sysdiagrams` or any other system tables added, e.g. `systranschemas` which is created when CDC support is added, would match any schema other than `dbo` anyway.) – Aaron Bertrand Jan 23 '14 at 15:33
12

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?

Community
  • 1
  • 1
Nandoviski
  • 338
  • 2
  • 12
5
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
Lorena Pita
  • 1,138
  • 1
  • 15
  • 17
2
SELECT *
FROM sys.tables t
INNER JOIN sys.objects o on o.object_id = t.object_id
WHERE o.is_ms_shipped = 0;
jtimperley
  • 2,394
  • 10
  • 10
0
select * from [schema_name].sys.tables

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

suvojit_007
  • 1,649
  • 2
  • 12
  • 21
Saman Kapali
  • 19