SQL Exercise – 20

SQL Exercise:

Retrieve a list of all tables along with the number of rows in each table in SQL Server.

Soluton:

To retrieve a list of all tables along with the number of rows in each table in SQL Server, you can query the system views sys.tables and sys.partitions, which store metadata about the tables and their partitions.

Here is the SQL query to get the list of all tables and the number of rows in each.

SELECT 
DB_NAME() AS DatabaseName,
t.name AS TableName,
SUM(p.rows) AS Row_Count
FROM 
sys.tables t
INNER JOIN 
sys.partitions p ON t.object_id = p.object_id
WHERE 
p.index_id IN (0, 1)
GROUP BY 
t.name
ORDER BY 
Row_Count DESC;

Output:

Explanation:

1. sys.tables: This system view contains a row for each table in the database.
2. sys.partitions: This system view contains information about the partitions of all tables and indexes. Each table has at least one partition (if there are no indexes, the table will be in a “heap”).
3. t.object_id = p.object_id: This join condition links each table with its partitions.
4. p.index_id IN (0, 1): We filter the rows to count only those for the heap (index_id = 0) and clustered index (index_id = 1), which are the main types of data storage for tables.
5. DB_NAME(): This function returns the name of the current database, which will be displayed along with the table name and row count.
6. SUM(p.rows): Sums the row counts across all partitions for each table.
7. GROUP BY t.name: Groups the results by table name, so each table is listed once with its total row count.
8. ORDER BY Row_Count DESC: Orders the tables by row count in descending order.