Each table wise total records in sql

To retrieve the total number of records (rows) for each table in a SQL Server database, you can use the sys.tables and sys.dm_db_partition_stats views. Here's a query to accomplish this:

---

Query to Get Row Counts for All Tables

SELECT 
    t.name AS TableName,
    SUM(p.rows) AS TotalRecords
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.object_id
WHERE 
    p.index_id IN (0, 1) -- 0 = Heap, 1 = Clustered index
GROUP BY 
    t.name
ORDER BY 
    TotalRecords DESC;

---

Explanation of Query:

1. sys.tables: Contains information about all tables in the database.
2. sys.partitions: Provides information about partitions of tables and indexes. The rows column contains the number of rows for each partition.
3. index_id IN (0, 1): Ensures you count rows only for heap (no clustered index) or clustered index partitions.

---

Sample Output:

TableName	TotalRecords
Customers	1200
Orders	450
Products	150