Record count in all tables

[digispace]

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.

Thanks.

[Erland Sommarskog]

digispace (di***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx