Total Page Preview: 000000019929
Find row count all tables with size from Database in SQL SERVER
Find row count all table with size from database in SQL Server.
Find table rows Using sys.sysindexes:
SELECT T.name AS [TABLE NAME],
I.rows AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.sysindexes AS I
ON T.object_id = I.id
AND I.indid < 2
ORDER BY I.rows DESC
OUTPUT:
Find table rows Using sp_spaceused
sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the space used each table (data and index).
Expample:
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
unused VARCHAR(18))
EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '
SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
CONVERT(bigint,left(reserved,len(reserved)-3)) AS 'SizeInKB'
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
DROP TABLE #RowCountsAndSizes
OUTPUT:
Using SSRS :-
Right click on database > Reports > Standerd Reports > Disk Usage by Table or Top Table :
OUTPUT:-
Thanks
Brijesh Kumar
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com