Total Page Preview:   000000016724

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:

Table Row Count In Database in sql server

 

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



Comments

SANNY KUMAR
01-Jul-2016
This is very useful article for Azure developer.
Anuj Panwar
23-Aug-2016
Good Article for DBA.
Jason Hong
26-Sep-2016
Thanks. This solved my needs instead of using Count(*) for all tables' rows inside a database which ended up with long running queries.
ITHUBCITY LTD.
13-Oct-2016
Thanks Jason Hong!
ITHUBCITY LTD.
13-Oct-2016
Thanks Jason Hong!
SANNY KUMAR
08-Sep-2016
How to get all Index in Database; SELECT IndexName = QUOTENAME(I.name), TableName = QUOTENAME(SCHEMA_NAME(T.[schema_id])) + N'.' + QUOTENAME(T.name), IsPrimaryKey = CASE WHEN I.is_primary_key = 1 THEN 'Yes' ELSE 'No' END FROM sys.indexes AS I INNER JOIN sys.tables AS T ON I.[object_id] = T.[object_id] WHERE I.type_desc <> N'HEAP' ORDER BY IsPrimaryKey ASC, TableName ASC, IndexName ASC;

                           
                           

                           

                           

Facebook User: