SQL Query to List Size of the Table with Row Counts

SQL Query to List Size of the Table with Row Counts


Sometimes we need simple query which can do the task, then complicated solution. Here is a simple query which list size of the table in MB with Row Counts. I often run at my customer understand how many different tables they have and what is the row counts as well as the size of the each table.
It is very powerful but simple query.


USE [YourDBName] -- replace your dbname
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO




    • Related Articles

    • SQL Queries to find the database size

      Environment: Not Environment Specific Problem Statement: SQL Queries to find the database size Solution: There are below SQL Queries , used to analyze the iVend database : SQL Query for database size SELECT DB_NAME(database_id) AS DBName,Name AS ...

    • compatibility version SAP & SQL Version

      Product Query Customer currently using the SQL version 2016 in HO and stores in 2008 express . SQL 2019 and higher version will support ivend 6.6? SAP Version 10 and Pl 2405 and higher PL version will compatible with ivend integration ? Answer As per ...

    • Microsoft SQL Server Database Maintenance activity

      Database health check & Optimization steps Steps and walkthrough to do a health check and optimization of iVend database. The entire process broadly consists of 8 steps: Step 1: Take Backup of DB. Open SQL Server Management Studio Express and connect ...

    • Consolidating Stock Take Counts

      Consolidating Stock Take Counts (Done by multiple users happening at different Locations) iVend 6.5 introduced a new feature where the stock take count can be done with multiple users for the same or different locations at a time and thereafter all ...

    • Steps to check the issue : 'Database size increase

      Scenario : If iVend database size is high in size, suggest to check the following information : Steps : 1) Where is the user getting this abnormal data in size (Enterprise or Store) 2) What is the SQL server version. (Express edition has a limit and ...