DISPLAY all tables from SQL database

Question

How do i make it work?

SELECT * FROM
(SELECT TABLE_NAME  FROM
    databasename.INFORMATION_SCHEMA.TABLES);

Alias your derived table. *Why* are you using a derived table at all though? Also, why use `SELECT *` when your derived table only has *one* column, `TABLE_NAME`? — Thom A, Mar 18 '22 at 12:22
I'm not crazy about the duplicate because the highly-voted and accepted answer uses less optimal and even deprecated methods. — Aaron Bertrand, Mar 18 '22 at 12:33
I want to display all tables listed on TABLE_NAME I'm trying to do something like that SELECT *FROM (all tables listed in the column of the other select) — Deirendor, Mar 18 '22 at 12:42
`I actually want the tables...` Do you mean you want to query for the table definitions themselves and not just the table names? — squillman, Mar 18 '22 at 12:56
I wanted was to display the actual table in the select not just make i don't know 30 selects at once but since no one understood what was asking i'll just do it in a hard way — Deirendor, Mar 18 '22 at 13:05
If I understand your last comment, you want to select the data from each of the tables all at once in a single select? That is never going to work. SQL just doesn't work that way. All results in a single SELECT statement have to fit within the same schema. — squillman, Mar 18 '22 at 13:12

Aaron Bertrand · Answer 1 · 2022-03-18T13:28:39.843

The simplest query is:

SELECT name FROM [databasename].sys.tables;

But the better query is to take schema into account:

SELECT [schema] = s.name,
       [table]  = t.name
FROM [databasename].sys.tables AS t
INNER JOIN [databasename].sys.schemas AS s
  ON t.[schema_id] = s.[schema_id];

You want to add a filter to prevent replication or diagram objects that you didn't create, e.g.

WHERE t.is_ms_shipped = 0;

If you're actually trying to say "I want all the data from all the tables" well, you'll need dynamic SQL for that, and I hope your tables aren't big.

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + char(39) + ts + char(39) + ';
  SELECT * FROM ' + ts + N';'
FROM 
(
  SELECT ts = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.is_ms_shipped = 0
) AS x

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

I would only use INFORMATION_SCHEMA if I really needed metadata queries that were limited in scope because they need to work on multiple platforms. See this blog post for more info.

DISPLAY all tables from SQL database

1 Answers1