Geek asks and answers

SQL: use the calculated row as the table name, join all selection rows for counting

I have access to MS SQL Server with MS Access through ODBC, and I want to display the table names, their column names and the number of rows in the table. Table names are stored in a table named "sys_tables", column names in "sys_columns". Unfortunately, the number of rows in the table must be taken into account. Since I am not testing SQL, my first attempt does not work:

SELECT t.name, c.name, t.object_id, x.cnt
FROM sys_tables AS t INNER JOIN sys_columns AS c ON t.object_id = c.object_id
LEFT OUTER JOIN (SELECT COUNT(*) AS cnt FROM @t.name AS tbl ON tbl.cnt > 0) AS x

What is the correct way to use a computed string in a SELECT table name? Can I make a sub select that selects all rows without a real relationship?

+3
source share
3 answers

. SQL.

, - sys, , .

0

SQL . SQL Server, ( ) SQL Server. SQL Server SQL ( , , SQL).

SQL Server sysindexes ( sys.indexes) rowcnt. 100% , .

, sp_MSForEachTable :

EXEC sp_msForEachTable
    'SELECT PARSENAME(''?'', 1),
    COUNT(*) FROM ?'

, ( ), Access , . .

+2

:

SELECT COUNT(t.name)
FROM sys_tables AS t INNER JOIN sys_columns AS c ON t.object_id = c.object_id
0

More articles:


All Articles