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
Here is the result which we receive if we run above query on AdventureWorks sample database.
When you run above query, it usually runs in just a split seconds and it also does not lock any of your essential tables while it is running (only shared lock for select statement), hence this query is very much alright to run on your production database. Trust me, this may be simple but very powerful script you must keep in your bookmark.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
16 Comments. Leave new
Here’s just rowcount but for all tables in all userdbs.
–Run in SQLCMD Mode.
–:Connect SQL1
SET NOCOUNT ON;
USE tempdb;
CREATE TABLE #AllDBRowCounts(DB sysname,SchemaName sysname,TableName sysname,RowCounts bigint);
INSERT INTO #AllDBRowCounts
EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
IF ”?” NOT IN (”master”,”model”,”msdb”,”tempdb”)
BEGIN
SELECT db_name() as DB,
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
END’;
SELECT * FROM #AllDBRowCounts ORDER BY DB,SchemaName,TableName;
DROP TABLE #AllDBRowCounts;
GO
Thank you!
You might mention that counts could be off in certain circumstances… and that DBCC UPDATEUSAGE would be required to get accurate numbers. With caveats on running it often, especially on large databases, of course.
Why would I get duplicates with this query? Is that because DBCC UPDATEUSAGE needs to be ran like Steve indicates? One of my tables is listed 3 times and each has different row counts.
Hi,
You should have some filtered index on your database.
Since the script used JOIN with sys.indexes and then GROUP BY p.rows, you have different result. Every with different (and real) usage.
Maybe you want to filter
i.type = 1
To only have info for CLUSTERED index.
To respect the goal of the script.
Could be better to change p.rows by MAX(p.rows) AS RowCounts,
and remove p.Rows from the GROUP BY clause.
What does this total pages contain? How SQL server decides to allocate how many pages to a table?,
One page is 8KB in size. If we insert more data and it is not able to fix it one page, it allocates next page and so one.
Would you explain that the sum of used_pages divide by 128 […(SUM(a.used_pages) / 128.00…]?
In SQL Server, the page size is 8-KB. This means SQL Server databases have 128 pages per megabyte.
https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-2017
This was really great. I added a filter for just the table name I wanted. Before I was using
exec sp_spaceused ‘[TableName]’
But i like this better! Can anyone educate me plus and minus of each approach? I am a T-SQL white belt!
i need with DB names as well.
This is the correct and updated script.
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(‘tempdb..#AllDBRowCounts’) IS NOT NULL
DROP TABLE #AllDBRowCounts
go
CREATE TABLE #AllDBRowCounts(
DB sysname,
SchemaName sysname,
TableName sysname,
RowCounts bigint,
Used_MB bigint,
UnUsed_MB bigint,
Total_MB bigint
);
INSERT INTO #AllDBRowCounts
EXEC master.sys.sp_MSforeachdb @command1 = N’USE [?];
IF “?” NOT IN (“master”,”model”,”msdb”,”tempdb”)
BEGIN
SELECT db_name() as DB,
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
END’;
SELECT * FROM #AllDBRowCounts ORDER BY DB,Total_MB DESC
DROP TABLE #AllDBRowCounts;
GO
I have a log table that has 58 GB of data and I am asked to recommend the history we need to hold with the size of this table not exceeding 5 GB. Is there a way I can query that reports me the volume of table based on a subset of records?
Thanks. It was very helpfull in my case where I should analyze database done by somebody else. Database is growing to much and to fast and to have an overview which tables are the biggest is good start point.
In reference to the spirit of the script I think the proper change to the p.rows is SUM(p.rows) and then remove the p.rows from the GROUP BY