Count number of rows across multiple tables in one query

Question

I have a SQL Server 2005 database that stores data for multiple users. Each table that contains user-owned data has a column called OwnerID that identifies the owner; most but not all tables have this column.

I want to be able to count number of rows 'owned' by a user in each table. In other words, I want a query that returns the names of each table that contains an OwnerID column, and counts the number of rows in each table that match a given OwnerID value.

I can return just the names of the matching tables using this query:

SELECT OBJECT_NAME(object_id) [Table] FROM sys.columns 
    WHERE name = 'OwnerID' ORDER BY OBJECT_NAME(object_id);

That query returns a list of table names like this:

+---------+
|  Table  |
+---------+
| Alpha   |
| Beta    |
| Gamma   |
| ...     |
+---------+

But is it possible to write a query that can also count the number of rows in each table that match a given OwnerID? ie:

+---------+------------+
|  Table  |  RowCount  |
+---------+------------+
| Alpha   | 2042       |
| Beta    | 49         |
| Gamma   | 740        |
| ...     | ...        |
+---------+------------+

Note: The list of table names needs to be returned dynamically, it is not suitable to hard-code table names into this query.

---

Edit: the answer...

(I can't edit your answers yet but I can edit my own question so I'm putting it here...)

Damien_The_Unbeliever had essentially the correct answer, but SQL Server doesn't allow string concatenation in an exec statement so I had to set the query prior to the exec statement. The final query is as follows:

DECLARE @OwnerID int;
SET @OwnerID = 1;

DECLARE @ForEachSQL varchar(100);
SET @ForEachSQL = 'INSERT INTO #t(TableName,RowsOwned) SELECT ''?'', COUNT(*) FROM ? WHERE OwnerID = ' + CONVERT(varchar(11), @OwnerID);

CREATE TABLE #t(TableName sysname, RowsOwned int);
EXEC sp_MSforeachtable @ForEachSQL, 
    @whereAnd = 'AND o.id IN (SELECT id FROM syscolumns where name=''OwnerID'')';
SELECT * FROM #t ORDER BY TableName;
DROP TABLE #t;

Solution 1

You can use sp_MSForeachtable, and the @whereand parameter, to specify a filter so you're only working against tables with an OwnerID column. Create a temp table, and populate that for each matching table. Something like:

create table #t(tablename sysname,Cnt int)
sp_MSforeachtable 'insert into #t(tablename,Cnt) select ''?'',COUNT(*) from ?',@whereAnd='and o.id in (select id from syscolumns where name=''OwnerID'')'
select * from #t

Two major caveats to mention - first is that sp_MSforeachtable is "undocumented", so you use it at your own risk - it could be suddenly removed from SQL Server by any kind of servicing, or in the next release.

The second is that, having a dynamic schema is usually a sign that something else has gone wrong in modelling - possibly attribute splitting (where sales for January and February are given different tables, even though they're logically the same thing and should appear in the same table, with possibly an additional column to distinguish them)

---

And, of course, you wanted to filter based on a particular clientID, so the query would be more like:

'insert into #t(tablename,Cnt) select ''?'',COUNT(*) from ? where OwnerID=' + @OwnerID

(Assuming @OwnerID is the owner sought, and is an int)

Solution 2

This would get the info from sysindexes. It can be slightly out of date but will give you a rough count

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

If you needed it to be 100% right then you could use the undocumented feature sp_MSForEachTable

DECLARE @SQL VARCHAR(255) 
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
    EXEC(@SQL) 

    CREATE TABLE #foo 
    ( 
        tablename VARCHAR(255), 
        rc INT 
    ) 

    INSERT #foo 
        EXEC sp_msForEachTable 
            'SELECT PARSENAME(''?'', 1), 
            COUNT(*) FROM ?' 

    SELECT tablename, rc 
        FROM #foo 
        ORDER BY rc DESC 

    DROP TABLE #foo 

Solution 3

You can use this:

DECLARE @nSQL NVARCHAR(MAX)

SELECT @nSQL = COALESCE(@nSQL + 'UNION ALL ' + CHAR(10), '') 
    + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) FROM ' + QUOTENAME(TABLE_NAME) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'strKey'

-- This will PRINT out the dynamically generated SQL statement. Just replace this with EXECUTE(@nSQL) when you are happy to run it.
PRINT @nSQL

Update: To search for a specific OwnerId:

DECLARE @nSQL NVARCHAR(MAX)
DECLARE @OwnerId INTEGER
SET @OwnerId = 1

SELECT @nSQL = COALESCE(@nSQL + 'UNION ALL ' + CHAR(10), '') 
        + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) FROM ' + QUOTENAME(TABLE_NAME) + ' WHERE OwnerId = @OwnerId' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'strKey'

EXECUTE sp_executesql @nSQL, '@OwnerId INTEGER', @OwnerId

Comments

[AakashM]

Also, if there are parent and child tables, the sum of rows is going to be an even less meaningful statistic than it already is.

[Damien_The_Unbeliever]

@AakashM - sorry, rolled back your edit, since we both edited at the same time. Have since re-added the link to "hidden features" - was that the only edit you did?

[gutch]

You're right about a dynamic schema usually being a sign modelling has gone wrong... but in this case I think we're using the power for good! This script is to be run as part of automated testing to determine whether code has changed any data that it shouldn't; the row counts will be checked before and after the run and should not change. The dynamic table lookup means that the test will automatically cover new tables without someone remembering to update the test.

[gutch]

Thanks — this answer worked just fine, but it does need to be changed to restrict to only the rows matching my desired OwnerID

[gutch]

+1 for PARSENAME(''?'', 1) — it gives much simpler output than the standard ''?''

[AdaTheDev]

@gutch - to complete my answer, I've updated to add the ability to do that :)