3

I have access to a MS SQL Server with MS Access via ODBC and I want to display the table names, their column names and the number of rows per table. The table names are stored in a table named "sys_tables", the column names in "sys_columns". Unfornunately the number of rows per table has to be counted. As I'm not experienced in SQL, my first try is not working:

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 right way to use a computed string in a SELECT as table name? Can I do a sub select that selects all rows without real relation?

Andreas
  • 1,220
  • 8
  • 21
  • [This question](http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-the-tables-in-a-sql-server-database) has other examples (n.b. not all answers work via Access over ODBC). – richaux May 26 '11 at 15:06
  • This is an interesting challenge. I'm curious to see how it can be done. Meanwhile I'm also curious how you intend to use this information in your Access application: the names of the tables in the SQL Server database; the names of the columns and the number of rows in each of those tables. – HansUp May 26 '11 at 16:39
  • Table names cannot be parameterized in Jet/ACE SQL, so the ultimate question cannot actually be answered. It can only be done by writing SQL on the fly. – David-W-Fenton May 28 '11 at 22:35

3 Answers3

2

You can't use a variable table name in a SQL statement on the server side. You will need to get the table names from SQL Server and then use those to send new queries (one for each table) back to SQL Server. You could also write a stored procedure on the SQL Server to handle this using dynamic SQL (just be sure that you are familiar with injection attacks whenever you are using dynamic SQL).

SQL Server stores some row count information in sysindexes (not to be confused with sys.indexes) in the rowcnt column. This information is not always 100% accurate though.

Finally, you can use the undocumented stored procedure sp_MSForEachTable like so:

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

Be aware that this last method will return multiple result sets (one for each table), so you need to handle it that way in Access or combine them together in a temporary table and then return them as one result set. You can do all of that in a stored procedure.

Tom H
  • 46,766
  • 14
  • 87
  • 128
0

Can't you do this:

SELECT COUNT(t.name)
FROM sys_tables AS t INNER JOIN sys_columns AS c ON t.object_id = c.object_id
waqasahmed
  • 3,555
  • 6
  • 32
  • 52
  • 1
    That will only return the number of columns in the database, not the counts for each table. – Tom H May 26 '11 at 14:58
0

You cannot use a variable for a table name. The only workaround is dynamic SQL.

However, I suspect that there's a table somewhere in sys which gives the row count for each table, though it may not be kept perfectly up-to-date.

  • The other correct answer recommends an undocumented stored procedure, but that is not available from MS Access via ODBC. – Andreas Jun 06 '11 at 09:10