Set table name in a SELECT statement from a nested select-CodePudding

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

CodePudding user response：

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])