- no there are no where clauses;
- It is always more than 1 table
- all tables have primary keys
Always more than 1 table. This implies (to me) that the number of rows returned may not be the same as the number of rows in a table. That's a shame, because if it was, we could use that to our advantage.
You see, primary keys are made up of one or more columns. PK's must be unique for the column(s). PK's have a unique index. And... finally... we can get the number of rows in the index. so...
Code:
Select Object_Name(id) As TableName,
RowCnt As Rows
From sysindexes
Where Status & 2048 = 2048
Order By TableName
The previous query will return a single row for each table that has a primary key and also return the number of rows in each table. Please note that this is an extremely efficient way to get row counts, but only for those tables that have a primary key.
** side note. Bit wise AND on the status column with value 2048 causes the results to be limited to primary keys.
If you cannot use the trick I just mentioned, then you have a couple options. You could rewrite the code to NOT use the stored procedures. This is likely to be the most efficient method because you can eliminate a lot of extra work.
Alternatively, you could modify the code I mentioned in my first response to put the data in to temp tables and then ignore it. Something like this:
Code:
Declare @Proc1Count Int
Declare @Proc2Count Int
Create Table #Temp1 (Col_1 Int, Col_2 VarChar(20))
Insert Into Table1 Exec Proc1
Select @Proc1Count = @@RowCount
Create Table #Temp2 (Col_1 Decimal(10,2), Col_2 bit)
Insert Into #Temp2 Exec Proc2
Select @Proc2Count = @@RowCount
The problem with this method is that is becomes dependent on the output structure of the stored procedures. For example, suppose you write all the code to get the counts from the 30 stored procedures. Everything works great. Then, 6 months from now, someone decides to add another column to the output of one of the procedures. Now the temp table you create does not match the output of the stored procedure and this code will break.
Bottom line, this code will be difficult to maintain.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom