Get RowCount for all the tables

Get RowCount for all the tables

 

Here are few ways of getting the Rowcount of the tables in the database.

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
1 ASC --DESC


---------------------
EXEC sp_spaceused 'tablename'
--------------------
CREATE PROCEDURE dbo.listTableRowCounts
AS
BEGIN
SET NOCOUNT ON

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
END
----------------------------
GO

CREATE PROCEDURE dbo.allTables_SpaceUsed
AS
BEGIN
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

CREATE TABLE #t
(
id INT,
TableName VARCHAR(32),
NRows INT,
Reserved FLOAT,
TableSize FLOAT,
IndexSize FLOAT,
FreeSpace FLOAT
)

INSERT #t EXEC sp_msForEachTable 'SELECT
OBJECT_ID(PARSENAME(''?'',1)),
PARSENAME(''?'',1),
COUNT(*),0,0,0,0 FROM ?'

DECLARE @low INT

SELECT @low = [low] FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'

UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM
(SELECT id, r = SUM(si.reserved), i = SUM(si.used)
FROM sysindexes si
WHERE si.indid IN (0, 1, 255)
GROUP BY id) x
WHERE x.id = #t.id

UPDATE #t SET TableSize = (SELECT SUM(si.dpages)
FROM sysindexes si
WHERE si.indid < 2
AND si.id = #t.id)

UPDATE #t SET TableSize = TableSize +
(SELECT COALESCE(SUM(used), 0)
FROM sysindexes si
WHERE si.indid = 255
AND si.id = #t.id)

UPDATE #t SET FreeSpace = Reserved - IndexSize

UPDATE #t SET IndexSize = IndexSize - TableSize

SELECT
tablename,
nrows,
Reserved = LTRIM(STR(
reserved * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
tablesize * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
indexSize * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
freeSpace * @low / 1024.,15,0) +
' ' + 'KB')
FROM #t
ORDER BY 1

DROP TABLE #t
END

 

--DB