Posted by Erland Sommarskog on 01/12/06 00:09

digispace (digispacetech@comcast.net) writes:
> There a stored procedure or query that will count all the records
> in all my tables in a database.
>
> One of these two -
>
> exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?'
>
> select object_name(id), rows from sysindexes where indid in (1, 0)
>
> Is there a way to add columns to the second query? I would like to see
> how many columns and their names as well.

It's not exactly clear how the output would look like. This query:

The second query could be extended to:

SELECT object_name(i.id), i.rows, colunms = COUNT(*)
FROM sysindexes i
JOIN syscolunms c ON i.id = c.id
WHERE i.indid IN (0, 1)
GROUP BY i.id, i.rows
ORDER BY 1

Gives you an approxamite row count and an exact column count.

SELECT object_name(i.id), i.rows, c.name
FROM sysindexes i
JOIN syscolunms c ON i.id = c.id
WHERE i.indid IN (0, 1)
ORDER BY 1, c.name

Lists the column names.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]