Total Page Preview:   000000017594

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;

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: