Rowcount in tables

Can someone throw light on how to get the rowcount of a table that is stored in any system tables? I want to get tablename and rowcount for all user tables in a database in a query. Is there anyway other than count(*)?
Thanks
VinnieDBCC UPDATEUSAGE (0) WITH COUNT_ROWS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_24rp.asp)

SELECT rows, Object_name(id)
FROM dbo.sysindexes
WHERE indid IN (0, 1)If you skip the DBCC, you'll get a SWAG guess, but none too accurate in a busy server.

-PatP|||sysindexes has a rowcount, see BOL for more details on this.

EDIT: sniped again!|||Thanks guys.|||Check for sp_spaceused. It may help you.|||Here's my little addtion to the pile: (built on MSSQL2k)

DECLARE @.MinSize dec(28,2), @.LikeName varchar(45), @.SizeSort bit, @.IncludeLogs bit
--DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

SET @.MinSize = .00
SET @.LikeName = '' --Company%'
SET @.SizeSort = 1
SET @.IncludeLogs = 0

/*
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)

** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
IF @.LikeName = '' SET @.LikeName = '%'

CREATE TABLE #space
(
name varchar(30),
id int,
type char(1),
rows int NULL,
reserved dec(28,2) NULL,
data dec(28,2) NULL,
datapages dec(28,2) NULL,
blob dec(28,2) NULL,
indexp dec(28,2) NULL,
unused dec(28,2) NULL,
rowsize dec(28,2) NULL
)

SET NOCOUNT ON

/************************************************** ***********
** Generate a list of all User and System tables.
** AND
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
INSERT INTO #space (name, id, type, reserved)
SELECT LEFT(obj.name,30), obj.id, obj.type, SUM(reserved)
FROM sysindexes idx, sysobjects obj
WHERE idx.id = obj.id
AND idx.indid IN (0, 1, 255)
AND obj.type in ('S','U')
AND obj.name != 'syslogs'
AND obj.name LIKE @.LikeName
AND (@.IncludeLogs = 1 OR obj.name NOT LIKE '%Log')
GROUP BY obj.name, obj.id, obj.type

/************************************************** ***********
** Initialize these to zero.
*/
UPDATE #space
SET rows = 0, data = 0, blob = 0, indexp = 0, unused = 0

/************************************************** ***********
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
UPDATE #space
SET data = data + ISNULL((
SELECT SUM(idx.dpages)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid < 2), 0)
FROM #space spc

UPDATE #space
SET blob = blob + ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid = 255), 0)
FROM #space spc

/************************************************** ***********
** index: sum(used) where indid in (0, 1, 255) - data space
*/
UPDATE #space
SET indexp = ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0) - data - blob
FROM #space spc

/************************************************** ***********
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
UPDATE #space
SET unused = reserved - ISNULL((
SELECT SUM(idx.used)
FROM sysindexes idx
WHERE id = spc.id
AND idx.indid IN (0, 1, 255)), 0)
FROM #space spc

/************************************************** ***********
** rows: rows where indid < 2
*/
UPDATE #space
SET rows = idx.rows
FROM #space spc, sysindexes idx
WHERE spc.id = idx.id
AND idx.indid < 2

/************************************************** ***********
** Page Size: for Windows NT
*/
DECLARE @.PageSize int, @.UsablePage int
SELECT @.PageSize = low
FROM master.dbo.spt_values
WHERE number = 1 AND type = 'E'

set @.UsablePage = @.PageSize - 132

/************************************************** ***********
** Compute the results
*/
UPDATE #space SET
reserved = reserved * @.PageSize,
datapages = data,
data = data * @.PageSize,
blob = blob * @.PageSize,
indexp = indexp * @.PageSize,
unused = unused * @.PageSize

UPDATE #space SET
rowsize =
CASE
WHEN rows < 50 THEN -1
ELSE ((data) / rows)
END

UPDATE #space SET
reserved = reserved / 1024.0 / 1024.0,
data = data / 1024.0 / 1024.0,
blob = blob / 1024.0 / 1024.0,
indexp = indexp / 1024.0 / 1024.0,
unused = unused / 1024.0 / 1024.0

/************************************************** ***********
** Finally: output the report header

*/

PRINT GETDATE()
PRINT ''

IF @.MinSize != 0
PRINT 'Tables with a Total space used of ' + LTRIM(STR(@.MinSize, 8,1)) + ' MB or greater in the ' + DB_NAME() + ' database.'
ELSE
PRINT 'All tables in the ' + DB_NAME() + ' database.'

PRINT ''

/************************************************** ***********
** Finally: output the totals
*/

DECLARE @.DataTotal dec(28,2), @.BlobTotal dec(28,2), @.IndexTotal dec(28,2), @.Format varchar(15)

SELECT @.DataTotal = SUM(data),
@.BlobTotal = SUM(blob),
@.IndexTotal = SUM(indexp)
FROM #space

SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal+@.Blob Total+@.IndexTotal),1)
PRINT 'Space used : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.DataTotal),1)
PRINT 'Space used by table data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.BlobTotal),1)
PRINT 'Space used by text/image data : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),convert(money,@.IndexTotal),1)
PRINT 'Space used by table indexes : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' MB'
SET @.Format = CONVERT(varchar(15),@.PageSize)
PRINT 'Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
SET @.Format = CONVERT(varchar(15),@.UsablePage)
PRINT 'Usable Page size : ' + REPLICATE(' ',15-DATALENGTH(@.Format)) + @.Format + ' Bytes'
PRINT ''

/************************************************** ***********

** Finally: output the detail
*/

update #space
set rowsize = CASE
WHEN rowsize < 0 THEN 0
WHEN (data) < .1 THEN 0
ELSE rowsize
END

SELECT
TableName = name,
Rows = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,rows),1)),
Total = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data+blob+indexp),1)),
Data = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data),1)),
Blob = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,blob),1)),
Indexes = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,indexp),1)),
RowBytes = convert(varchar(11),
CASE
WHEN rowsize = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rowsize),1)
END),
RowsPage = convert(varchar(11),
CASE
WHEN rows = 0 THEN 'n/a'
ELSE CONVERT(varchar(15),convert(money,rows/datapages),1)
END),
Pages = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,datapages),1)),
TotalSize = data+indexp+blob
INTO #report
FROM #space
WHERE data+indexp >= @.MinSize AND type = 'U' and name != 'dtproperties'

UPDATE #report
SET Rows = REPLACE(REPLACE(Rows,'.00',''),'.0',''),
Pages = REPLACE(REPLACE(Pages,'.00',''),'.0','')

UPDATE #report
SET Rows = REPLICATE(' ',11-DATALENGTH(Rows)) + Rows,
Total = REPLICATE(' ',11-DATALENGTH(Total)) + Total,
Data = REPLICATE(' ',11-DATALENGTH(Data)) + Data,
Blob = REPLICATE(' ',11-DATALENGTH(Blob)) + Blob,
Indexes = REPLICATE(' ',11-DATALENGTH(Indexes)) + Indexes,
RowsPage = REPLICATE(' ',11-DATALENGTH(RowsPage)) + RowsPage,
Pages = REPLICATE(' ',11-DATALENGTH(Pages)) + Pages,
RowBytes = REPLICATE(' ',11-DATALENGTH(RowBytes)) + RowBytes

PRINT ' --- Average --- ----- Table Space In MB -----'

IF @.SizeSort = 1
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TotalSize DESC
ELSE
SELECT
TableName,
' Row Size' = RowBytes,
' Rows/Page' = RowsPage,
' Data Pages' = Pages,
' Rows' = Rows,
' Total' = Total,
' Data' = Data,
' Text/Img' = Blob,
' Index' = Indexes
FROM #report
ORDER BY TableName

PRINT ''

DROP TABLE #space
DROP TABLE #report|||Here's my little addtion to the pile: (built on MSSQL2k)Great zot there, bubba! I'd consider that a pile all its own, not an addition to an existing pile! Whew, I can't wait to see a major contribution!

-PatP|||I got the following errors when I ran this in Query Analyzer:

Server: Msg 137, Level 15, State 2, Line 172
Must declare the variable '@.Blob'.
Server: Msg 170, Level 15, State 1, Line 201
Line 201: Incorrect syntax near 'ney'.|||i think you might have a simple wrapping issue...@.blob isnt a variable in this script but @.blobtotal is.

testing it...that is exactly what happened...

any tips on posting this without having the format chang on me?|||try the attachment...

for some reason pasting it FROM this forum adds some freaky yeaky spaces.

they dont appear as spaces in the forum window, but they sure as hell get added if you copy/paste it out of here...

...sorry...forum noooob issue.|||Cool that worked. Check this script out that I've been using.sql