Incorrect syntax error using sp_MSforeachtable

Question

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

CloudyMarble · Accepted Answer · 2013-01-08 10:36:32Z

3

Try this:

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

answered Jan 8, 2013 at 10:36

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Pratik Over a year ago

What do you mean by "sysindexes.IndId < 2" & why?

CloudyMarble Over a year ago

Only for Heap and clustered Indexes

Pratik Over a year ago

Will that include all the tables? Tables which will not have any indexes not even primary key?

CloudyMarble Over a year ago

Yes they will be included too

Darkzaelus Over a year ago

Looks like it isn't realtime, but could be made to be so: sql-server-performance.com/forum/threads/sysindexes-table.13767 not sure how this would affect performance though!

|

dove · Accepted Answer · 2013-01-08 10:53:31Z

3

I guess I got it :

exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' 
  as table_name from ?'

edited Jan 8, 2013 at 10:53

dove

20.8k14 gold badges88 silver badges109 bronze badges

answered Jan 8, 2013 at 10:37

Pratik

11.8k22 gold badges71 silver badges99 bronze badges

1 Comment

khichar.anil Over a year ago

It helped me to update statistics of all tables in one shot as: EXEC sp_MSforeachtable 'Update Statistics ? with FULLSCAN';

Dibstar · Accepted Answer · 2013-01-08 10:39:10Z

0

Try this instead:

sp_MSforeachtable 'select ''?'' Tablename, count(*) ''Rows'' from ?'

answered Jan 8, 2013 at 10:39

Dibstar

2,3742 gold badges25 silver badges38 bronze badges

Collectives™ on Stack Overflow

Incorrect syntax error using sp_MSforeachtable

3 Answers 3

13 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related