Search code examples
sql-servert-sqlsql-server-2000

Incorrect syntax error using sp_MSforeachtable

I plan to get all tables with their total row count in sql server 2000.

For which I did :

sp_msforeachtable 'select count(*) from ?'

In this column header is not mentioned, because of which its not distinguishable to which row count belongs to which table

For which I altered this by :

sp_msforeachtable 'select count(*) as ? from ?'

But its throwing an error as :

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

Could you please guide on this

Solution

  • Try this:

    SELECT
    sysobjects.Name, sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = 'U'
    AND sysindexes.IndId < 2