counting records in all tables

Hello all,

I was wondering if there is anyway to find number of records in all tables instead of count one table at a time by select count(*) from table_name.

thanks

The system tables are good for this. They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

I use the following to get the row count, data bytes, and index bytes for all tables:

SELECT

sys.schemas.[name] AS [Schema],

sys.tables.name AS [Table],

COALESCE([Row Count].[Count], 0) AS [Rows],

COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

FROM sys.tables

INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

LEFT OUTER JOIN (

SELECT

object_id,

SUM(rows) AS [Count]

FROM sys.partitions

WHERE index_id < 2

GROUP BY object_id

) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

ORDER BY sys.tables.[name]

(The weird text editor on this site messed up the formatting of the code; I apologize)

-Ryan

|||

Hello Ryan and all,

I am using SQL Server 2000; therefore, I won't be able to see table as sys.schemas and sys.tables. Would you advise how to do this in SQL Server 2000 ?

thanks

|||You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

Just wondering: why are you interested in this information?

Thanks,|||

hello Marcel and all,

I know there will be no guarantee unless using select(*). However, estimate number would be fine. Because I am looking for large table to tune.

|||

Unfortunately, we're fully upgraded to SQL 2005 here (we were active in the beta and were ready to go when 2005 was released). So I can't accurately convert it back to 2000 code.

While SQL 2000 doesn't have a sys.tables, it does have a systables. It should be possible to get this working on the older version with minor adjustments.

-Ryan

|||This is a quick and dirty procedure that uses the results from

sp_spaceused to create a simple report. I've used it to weed out a few

space hogs on a 2000 server. Note that getting accurate row counts is

probably contingent upon having your usage statistics up to date (see

DBCC UPDATEUSAGE). Create the procedure in master, and run it from

within the database you want to profile, like other sp_ procedures.

Tweak it to suit your needs - I'm sure there's room for improvement.

CREATE PROCEDURE sp_tablesizes @.biggestfirst bit = 0
AS
-- sp_tablesizes
-- Dave Britten, 2007
-- Produces a list of all user and system tables in the current database,
-- optionally sorted by physical size. Also returns grand totals in a
-- second result set.

SET NOCOUNT ON
CREATE TABLE #tablelist (
Owner sysname,
Name sysname
)

CREATE TABLE #tabledata (
Name varchar(128),
Rows char(11),
reserved varchar(18),
Data varchar(18),
index_size varchar(18),
Unused varchar(18)
)

CREATE TABLE #sizedata (
rows int,
reserved int,
Data int,
index_size int,
Unused int
)

DECLARE @.dbname sysname
SET @.dbname = db_name()
DECLARE @.tablename sysname
DECLARE @.ownername sysname
DECLARE @.tablestring varchar(261)

INSERT INTO #tablelist
EXEC('SELECT sysusers.name, sysobjects.name FROM [' + @.dbname + ']..sysusers INNER JOIN [' + @.dbname + ']..sysobjects ON sysobjects.uid = sysusers.uid WHERE type IN (''U'', ''S'') AND sysobjects.name NOT LIKE ''tempdb..%''')

DECLARE tables CURSOR FAST_FORWARD FOR
SELECT Owner, Name FROM #tablelist
OPEN tables

FETCH NEXT FROM tables INTO @.ownername, @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tablestring = '[' + @.ownername + '].[' + @.tablename + ']'
INSERT INTO #tabledata
EXEC sp_spaceused @.tablestring
FETCH NEXT FROM tables INTO @.ownername, @.tablename
END

CLOSE tables
DEALLOCATE tables

INSERT INTO #sizedata (rows, reserved, data, index_size, unused)
SELECT
rows,
CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int),
CAST(LEFT(data, PATINDEX('% KB', data)) AS int),
CAST(LEFT(index_size, PATINDEX('% KB', index_size)) AS int),
CAST(LEFT(unused, PATINDEX('% KB', unused)) AS int)
FROM #tabledata

IF @.biggestfirst = 1
SELECT * FROM #tabledata ORDER BY CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int) DESC
ELSE
SELECT * FROM #tabledata ORDER BY name ASC

SELECT
SUM(rows) [Total Rows],
STR(SUM(reserved)) + ' KB' [Total reserved],
STR(SUM(data)) + ' KB' [Total Data],
STR(SUM(index_size)) + ' KB' [Total index_size],
STR(SUM(unused)) + ' KB' [Total Unused]
FROM #sizedata

DROP TABLE #tablelist
DROP TABLE #tabledata
DROP TABLE #sizedata

GO|||

Hello davidbrit2 and all,

Thank you davidbrit2 for the replied. When I complied the stored procedure, I got the problem at database name as a variable.

e.g: DECLARE @.dbName AS VARCHAR(30)

SELECT @.dbName = 'Another Database'

SELECT name FROM [' + @.dbName + ']..sysobjects WHERE type = 'U' ORDER BY name

Then I received error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '' + @.dbName + '..sysobjects'.

Please advise.

|||

For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

SELECT OBJECT_NAME(si.[id]), si.[rows]

FROM dbo.sysindexes si

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want to include the tables' owners then you could use this:

SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

FROM dbo.sysindexes si

INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]

INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want accurate row counts and can't be bothered creating and using a cursor then use this:

CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, TableRowCount

FROM #tmpOutput

ORDER BY 1

DROP TABLE #tmpOutput

Chris

|||

Here is the script that can give you the number of counts in each table -

Declare @.testSql varchar(300)
Create Table #TempTabCount(TABLE_QUALIFIER sysname NULL,
TABLE_OWNER sysname NULL,
TABLE_NAME sysname NULL,
NON_UNIQUE smallint NULL,
INDEX_QUALIFIER sysname NULL,
INDEX_NAME sysname NULL,
TYPE smallint NULL,
SEQ_IN_INDEX smallint NULL,
COLUMN_NAME sysname NULL,
COLLATION char(1) NULL,
CARDINALITY int NULL,
PAGES int NULL,
FILTER_CONDITION varchar(128) NULL)

Declare TAB_CURSOR CURSOR for
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES

Open TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @.testSql
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTabCount(TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION)
exec sp_statistics @.testSql
FETCH NEXT FROM TAB_CURSOR INTO @.testSql
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
Select TABLE_OWNER, TABLE_NAME, CARDINALITY from #TempTabCount

See if it solves your purpose. I tested it in SQL 2000 and working for me.

Enjoy!

Ash

|||Chris,

Ah-ha, it's sysindexes. I knew they had to be storing that information SOMEWHERE. ;)|||

Hi All,

Please find the query which will help to find the total number of records in all the tables in a SQL Server Database.

Select substring(obj.name, 1, 50) as Table_Name,
ind.rows as Number_of_Rows
from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id
where
obj.xtype = 'u'and ind.indid < 2
order by
obj.name

Regards
AKMEHTA