How to fetch the row count for all tables in a SQL SERVER database

I am searching for a SQL Script that can be used to determine if there is any data (i.e. row count) in any of the tables of a given database.

The idea is to re-incarnate the database in case there are any rows existing (in any of the database).

The database being spoken of is Microsoft SQL SERVER.

Could someone suggest a sample script?

The following SQL will get you the row count of all tables in a database:

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

If you want to by pass the time and resources it takes to count(*) your 3million row tables. Try this per SQL SERVER Central by Kendal Van Dyke.

---

Row Counts Using sysindexes If you're using SQL 2000 you'll need to use sysindexes like so:

-- Shows all user tables and row counts for the current database 
-- Remove OBJECTPROPERTY function call to include system objects 
SELECT o.NAME,
  i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

If you're using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice you should use the DMVs instead, like so:

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name,
  ddps.row_count 
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Works on Azure, doesn't require stored procs.

SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
AND t.type_desc = 'USER_TABLE'
AND t.name not like '%dss%'
AND s.index_id IN (0,1)

Credit.

This one looks better than the others I think.

USE  [enter your db name here]
GO

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        --A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
          A.Name, AVG(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
GO

Short and sweet

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

Output:

SELECT 
	sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM 
	sys.tables ta
INNER JOIN sys.partitions pa
	ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
	ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

SQL Server 2005 or later gives quite a nice report showing table sizes - including row counts etc. It's in Standard Reports - and it is Disc Usage by Table.

Programmatically, there's a nice solution at: http://www.sqlservercentral.com/articles/T-SQL/67624/

Don't use SELECT COUNT(*) FROM TABLENAME, since that is a resource intensive operation. One should use SQL Server Dynamic Management Views or System Catalogs to get the row count information for all tables in a database.

I would make a minor change to Frederik's solution. I would use the sp_spaceused system stored procedure which will also include data and index sizes.

declare c_tables cursor fast_forward for
select table_name from information_schema.tables




open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename




while @@fetch_status = 0
begin



select @stmt = 'sp_spaceused ' + @tablename 

exec sp_executesql @stmt

fetch next from c_tables into @tablename 




end




close c_tables
deallocate c_tables

close c_tables
deallocate c_tables

Here's a dynamic SQL approach that also gives you the schema as well:

DECLARE @sql nvarchar(MAX)

SELECT
	@sql = COALESCE(@sql + ' UNION ALL ', '') +
		'SELECT
			''' + s.name + ''' AS ''Schema'',
			''' + t.name + ''' AS ''Table'',
			COUNT(*) AS Count
			FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
	FROM sys.schemas s
	INNER JOIN sys.tables t ON t.schema_id = s.schema_id
	ORDER BY
		s.name,
		t.name

EXEC(@sql)

If needed, it would be trivial to extend this to run over all databases in the instance (join to sys.databases).

select all rows from the information_schema.tables view, and issue a count(*) statement for each entry that has been returned from that view.

declare c_tables cursor fast_forward for
select table_name from information_schema.tables

open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename

while @@fetch_status = 0
begin
		
	select @stmt = 'select @rowcount = count(*) from ' + @tablename
	
	exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT

    print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'
	
	fetch next from c_tables into @tablename

end

close c_tables
deallocate c_tables

This is my favorite solution for SQL 2008 , which puts the results into a "TEST" temp table that I can use to sort and get the results that I need :

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
DROP TABLE #t;
CREATE TABLE #t 
( 
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18), 
data VARCHAR(18), 
index_size VARCHAR(18),
unused VARCHAR(18)
) ;
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
SELECT * INTO TEST FROM #t;
DROP TABLE #t;
SELECT  name, [rows], reserved, data, index_size, unused FROM TEST \
WHERE ([rows] > 0) AND (name LIKE 'XXX%')

	SELECT
		  SUM(sdmvPTNS.row_count) AS [DBRows]
	FROM
		  sys.objects AS sOBJ
		  INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
				ON sOBJ.object_id = sdmvPTNS.object_id
	WHERE 
		  sOBJ.type = 'U'
		  AND sOBJ.is_ms_shipped = 0
		  AND sdmvPTNS.index_id < 2
	GO