Set table name in a SELECT statement from a nested select

Question

I have a table in MSSQL which has been called tables_list, it has a list of tables. How can I use this list in another SELECT to count number of rows in each table and reach a list like the following picture:

SELECT T.[TABLE_NAME], count(*) AS NUMBERS
FROM [db_name].[schema_name].[T.[TABLE_NAME]]
(SELECT [TABLE_NAME],
  FROM [db_name].[schema_name].[tables_list]) T

Do they need to be accurate counts or are estimates acceptable? — Thom A, Oct 15 '21 at 13:54
Also do you *really* have a table called `T.[TABLE_NAME]`? That's, with respect, a very foolish name if so. — Thom A, Oct 15 '21 at 13:55
For an estimate see this question: [SQL count rows in a table](https://stackoverflow.com/questions/28916917/sql-count-rows-in-a-table) — Thom A, Oct 15 '21 at 13:56
@DougCoats is this is the case, I can use the nested SELECT in WHERE condition. — MJBZA, Oct 15 '21 at 13:57
`FROM [dbo].[schema].[tables_list]` also do you have a database called `dbo` and with a schema called `schema`? — Thom A, Oct 15 '21 at 13:58
Calling your database `dbo`, when that's the default schema in a database, doesn't make thing clearer; it actually makes things more confusing. — Thom A, Oct 15 '21 at 14:07
@Larnu what does your implementation for estimates look like? I'm curious how you could get a non-precise answer — SCCJS, Oct 15 '21 at 14:07

score 1 · Accepted Answer · edited Oct 15 '21 at 14:10

in sql server you can use system partition table to get the row counts in a database :

SELECT * FROM
(SELECT
    QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],
    SUM(sPTN.Rows) AS [RowCount]
FROM
    sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
WHERE
    sOBJ.type = 'U'
    AND sOBJ.is_ms_shipped = 0x0
    AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
    sOBJ.schema_id,
    sOBJ.name
ORDER BY [TableName]) X
WHERE X.[TableName] IN 
(SELECT 
CONCAT('[schema_name].[prefix', [TABLE_NAME], ']') 
FROM [db_name].[schema_name].[tables_list])

Set table name in a SELECT statement from a nested select

1 Answers1