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

Many times you have requirement to determine if any data or row count in a given database.

So, today I am scripting this scenario to fetch all data or row count in database. for this I will use sys.tables, sys.indexes, sys.partitopns, sys.allocation_units



Script 1:

SELECT t.name TableName, i.rows Records
FROM sysobjects t, sysindexes i
WHERE t.xtype='U' AND i.id = t.id AND i.indid in(0,1)
ORDER BY TableName

Script 2:

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


script3:
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

Script 4:

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        A.Name, SUM(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

Script 5: Broadview

SELECT t.name[TableName], i.name[IndexName], p.[rows], SUM(a.total_pages)[ToalPages],
SUM(a.used_pages)[UsedPages],SUM(a.data_pages)[DataPages],
(SUM(a.total_pages)*8/1024)[TotalSpaceMB],
(SUM(a.used_pages)*8/1024)[UsedSpaceMB],
(SUM(a.data_pages)*8/1024)[DataSpaceMB]
 
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id =i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id =a.container_id
WHERE
t.name NOT LIKE 'dt%' AND
i.object_id>255 AND
i.index_id<=1
GROUP BY
t.name, i.object_id, i.index_id, i.name,p.[rows]
ORDER BY OBJECT_NAME(i.object_id)


you can get more solutions by manipulating sys table carefully.