SQL get table row count for specific table in DB

Question

How Can i iterate thru the all my DB's and get a row count for each employee table? Each client is has there own DB, need to find the total employees in each DB.

Been trying to figure out how to use sp_MSforeachdb

sp_MSforeachdb 
@command1 = 'select count(*) from employee'

Can output in seperate tables or would be good in one table wiht the DB name.

Almost all the DB's are the same, only one is different has no employee table. — Yogurt The Wise
– Yogurt The Wise, Commented Jan 19, 2012 at 22:00

Yogurt The Wise · Accepted Answer · 2012-01-19 23:55:01Z

1

How about

    DECLARE @sql nvarchar(1000)
    SET @sql = 'Use [?];'
      + 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''dbo'' AND  TABLE_NAME = ''employee''))'
      + ' BEGIN'
      + ' SELECT COUNT(*) from [employee]'
      + ' END'
   exec sp_MSforeachDB @sql

TABLE_SCHEMA = ''dbo'' is optional here in most cases...

edited Jan 19, 2012 at 23:55

Yogurt The Wise

4,4995 gold badges36 silver badges43 bronze badges

answered Jan 19, 2012 at 22:16

perfectionist

4,3561 gold badge25 silver badges34 bronze badges

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

2 Comments

perfectionist Over a year ago

Thanks to joew's blog for syntax for "use [?];" and to akmad on another stack overflow question for the check if a table exists.

Yogurt The Wise Over a year ago

Thanks very much. I also added to mine ''?'' to the select. SELECT ''?'',COUNT(*) to get the table names. Those are single quotes.

user325117 · Accepted Answer · 2012-01-19 22:14:29Z

1

You need to tell it which database to use first (it's in ?):

EXEC sp_MSforeachdb
@command1='use ?; select count(*) from employee'

answered Jan 19, 2012 at 22:14

user325117

Collectives™ on Stack Overflow

SQL get table row count for specific table in DB

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related