get all table names with the primary key row count

Question

I have a scenario, where I need to compare data between two servers.

Long story short, I need a result set which gives the output like

SchemaName|TableName|RowCount_PrimaryKey

Whenever a new student joins a unique schema is created say Stu1200 and this schema has 5 tables like

stu1200.class
stu1200.sub
stu1200.avg
stu1200.work
stu1200.blabla

There are 500 students in our database. So 500 schema. I need to compare all the tables of work of 500 students.

In order to do so I need to get the counts of the primary key, say StuID. So I need to get all the 500 schemas with the table like work.

Something like

SchemaName  TableName   StuID_Count
stu1200      Work         70
Stu1201      Work         112
Stu1202      Work         9

How can this be done? I have a script which does row counts of the table but its of useless, I need the row counts based only on the primary key.

Note: using SQL Server 2000 :(

Thanks in advance for sharing your suggestions/experience.

Answer 1

Your design is highly questionable but here is a way to get a relatively up-to-date count in an efficient manner. In SQL Server 2005+:

DECLARE @tablename SYSNAME;
SET @tablename = N'Work';

SELECT 
  SchemaName = OBJECT_SCHEMA_NAME([object_id]), 
  TableName = @tablename, 
  RowCount_PrimaryKey = SUM([rows])
FROM sys.partitions
  WHERE OBJECT_NAME([object_id]) = @tablename
  AND index_id IN (0,1)
  GROUP BY OBJECT_SCHEMA_NAME([object_id])
  ORDER BY SchemaName;

Just noticed SQL Server 2000.

DECLARE @tablename SYSNAME;
SET @tablename = N'Work';

SELECT
  SchemaName = u.name,
  TableName = @tablename,
  i.rows
FROM 
  sys.sysindexes AS i
INNER JOIN sys.sysobjects AS o
  ON i.id = o.id
INNER JOIN sys.sysusers AS u
  ON o.uid = u.uid
  WHERE i.indid IN (0,1)
  AND o.name = @tablename;