List all tables with row-count and size on a database.

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB desc

How to find Row count for all tables in a database – SQL Server

SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id)) + ‘.’ + QUOTENAME(O.name) AS [TableName]
, SUM(P.Rows) AS [RowCount]
FROM
sys.objects AS O
INNER JOIN sys.partitions AS P ON O.object_id = P.object_id
WHERE
O.type = ‘U’ AND index_id < 2
GROUP BY
O.schema_id
, O.name
ORDER BY [TableName]
GO

—————————————————————–

Below script will give you index wise row count.

USE [Adventureworks];
GO
SELECT  OBJECT_NAME([p].[object_id]) AS [Table] ,
        [p].[index_id] AS [Index ID] ,
        [i].[name] AS [Index] ,
        [p].[rows] AS "Number of Rows"
FROM    [sys].[partitions] AS [p]
        JOIN [sys].[indexes] AS [i] ON [p].[object_id] = [i].[object_id]
                                  AND [p].[index_id] = [i].[index_id]
WHERE   [p].[object_id] = OBJECT_ID('dbo.sales');