Introduction
In this article we will take a look at different options that are available to get rowcount for all the tables in a SQL Server Database. Options mentioned in this article will have least impact when the queries are executed as these queries leverage system tables to fetch the rowcount data. One should always avoid using the costliest options which is SELECT COUNT(*) to fetch the rowcount data.
Let us take a look at each of the below mentioned methods to get the total number of rows in each of the SQL Server Tables in a database.
1. Using sys.objects and sys.partitions Catalog views available in SQL Server 2005 and above.
2. Using sys.dm_db_partition_stats DMV which is available in SQL Server 2005 and above.
3. Using sysobjects and sysindexes system view available in SQL Server 2000.
4. Quickly Get Row Count of all Table in SQL Server Database Using In-built SQL Server Reports.
5. Using SP_SPACEUSED System Stored Procedure.
6. Quickly Get Record Count of a Single Table using SP_SPACEUSED System Stored Procedure.
2. Using sys.dm_db_partition_stats DMV which is available in SQL Server 2005 and above.
3. Using sysobjects and sysindexes system view available in SQL Server 2000.
4. Quickly Get Row Count of all Table in SQL Server Database Using In-built SQL Server Reports.
5. Using SP_SPACEUSED System Stored Procedure.
6. Quickly Get Record Count of a Single Table using SP_SPACEUSED System Stored Procedure.
Using sys.objects and sys.partitions Catalog Views
Execute the below mentioned TSQL code which leverages sys.objects and sys.partitions catalog views to List all Tables with Rowcount in SQL Server Database.
/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */
Use AdventureWorks2008
GO
GO
SELECT
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
, (SPS.Rows) AS [Row Count]
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sys.objects AS SOS
INNER JOIN sys.partitions AS SPS
ON SOS.object_id = SPS.object_id
WHERE
SOS.type = 'U'
AND SPS.index_id < 2
ORDER BY [Table Name]
GO
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
, (SPS.Rows) AS [Row Count]
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sys.objects AS SOS
INNER JOIN sys.partitions AS SPS
ON SOS.object_id = SPS.object_id
WHERE
SOS.type = 'U'
AND SPS.index_id < 2
ORDER BY [Table Name]
GO
Using sys.dm_db_partition_stats Dynamic Management View
Execute the below mentioned TSQL code which leverages sys.dm_db_partition_stats Dynamic Management View (DMV) to get record count for all tables in a database.
/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */
Use AdventureWorks2008
GO
SELECT
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
, (SPS.Rows) AS [Row Count]
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sys.objects AS SOS
INNER JOIN sys.partitions AS SPS
ON SOS.object_id = SPS.object_id
WHERE
SOS.type = 'U'
AND SPS.index_id < 2
ORDER BY [Table Name]
GO
GO
SELECT
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
, (SPS.Rows) AS [Row Count]
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sys.objects AS SOS
INNER JOIN sys.partitions AS SPS
ON SOS.object_id = SPS.object_id
WHERE
SOS.type = 'U'
AND SPS.index_id < 2
ORDER BY [Table Name]
GO
Using sysobjects and sysindexes system views
Execute the below mentioned TSQL code which leverages sysobjects and sysindexes system views to retrieve the Row Count for all Tables in a Database. The below script works on SQL Server 2000.
/* Retrieve Row Count for all Tables in a Database - Backward Compatibility Script for SQL Server 2000 */
Use AdventureWorks2008
GO
GO
SELECT
SOS.Name
, SIS.Rows
, [Fill Factor] = CASE SIS.OrigFillFactor WHEN 0 THEN '100' ELSE OrigFillFactor END
, [Heap / Clustered Table] = CASE SIS.indid WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sysobjects SOS
INNER JOIN sysindexes
SIS ON SOS.id = SIS.id
WHERE
type = 'U' AND SIS.IndId < 2
ORDER BY SOS.Name
GO
SOS.Name
, SIS.Rows
, [Fill Factor] = CASE SIS.OrigFillFactor WHEN 0 THEN '100' ELSE OrigFillFactor END
, [Heap / Clustered Table] = CASE SIS.indid WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
FROM
sysobjects SOS
INNER JOIN sysindexes
SIS ON SOS.id = SIS.id
WHERE
type = 'U' AND SIS.IndId < 2
ORDER BY SOS.Name
GO
Rowcount for all tables in SQL Server Database
By: Editor Feb 2, 2010Page: 2/2 Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports
You can Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports.
1. Click View -> Object Explorer Details or Press F7 to open up Object Explorer Details.
2. Expand Database Node -> Expand Database -> Click Tables on the left side panel.
3. Right click any column in the right side panel and then choose Row Count as shown in the below snippet to display row count for all the tables within the choosen SQL Server Database.
2. Expand Database Node -> Expand Database -> Click Tables on the left side panel.
3. Right click any column in the right side panel and then choose Row Count as shown in the below snippet to display row count for all the tables within the choosen SQL Server Database.
Using SP_SPACEUSED System Stored Procedure
Execute the below mentioned TSQL code which leverages SP_SPACEUSED System Stored Procedure to get rowcount of all tables in SQL Server Database.
/* Get Rowcount of all tables in a SQL Server Database */
Use AdventureWorks2008
GO
GO
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL
DROP TABLE dbo.RowCounts;
GO
DROP TABLE dbo.RowCounts;
GO
CREATE TABLE RowCounts
( [TableName] VARCHAR(150), [RowCount] INT
, [Reserved] NVARCHAR(50)
, [Data] NVARCHAR(50)
, [Index_Size] NVARCHAR(50)
, [UnUsed] NVARCHAR(50))
GO
( [TableName] VARCHAR(150), [RowCount] INT
, [Reserved] NVARCHAR(50)
, [Data] NVARCHAR(50)
, [Index_Size] NVARCHAR(50)
, [UnUsed] NVARCHAR(50))
GO
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
EXEC sp_MSforeachtable 'sp_spaceused "?"'
GO
EXEC sp_MSforeachtable 'sp_spaceused "?"'
GO
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM RowCounts
ORDER BY [TableName]
GO
FROM RowCounts
ORDER BY [TableName]
GO
Quickly Get Total number of records in a Table using sp_spaceused system stored procedure
In case if you would like to know the total number of rows available in a table then execute the below mentioned TSQL code as this has least impact on the SQL Server Performance.
/* Total Row count and Space Used by a tables in SQL Server Database */
Use AdventureWorks2008
GO
GO
sp_spaceused [Person.Address]
GO
GO
Conclusion
In this article you have seen how one can utilize in built system catalogs, dynamic management views, system stored procedures to know the total number of rows available in a table without impacting the overall SQL Server Performance. It is advisable not to use SELECT COUNT(*) FROM TABLENAME query as it will impact the overall performance of SQL Server especially when you are queries aganist a very large table.
Read more: http://www.mytechmantra.com/LearnSQLServer/RowCount_of_all_Tables_P2.html#ixzz3h4tp3gy5
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
Read more: http://www.mytechmantra.com/LearnSQLServer/RowCount_of_all_Tables_P1.html#ixzz3h4tf045x
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
Row Count of All Tables in a Database and size of tables
1. select '['+SCHEMA_NAME(t.[SCHEMA_ID])+'].['+t.NAME+']' AS [fulltable_name],
SCHEMA_NAME(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows
from sys.tables t
INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)order by t.NAME
2.
DECLARE @TABLESSIZE TABLE (TableId INT identity(1,1),TableName varchar(500),RowCounts int,Reserved varchar(50),Data varchar(50),index_size varchar(50),unused varchar(50))
DECLARE @TABLELIST TABLE (TableId INT identity(1,1),TableName varchar(500))
DECLARE @TABLENAME VARCHAR(500)
DECLARE @FLGTAB INT
DECLARE @CNTTAB INT
INSERT INTO @TABLELIST select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME like 'TOP%'
SET @FLGTAB=1
SELECT @CNTTAB=COUNT(1) FROM @TABLELIST
WHILE(@FLGTAB<=@CNTTAB)
BEGIN
SELECT @TABLENAME=TableName from @TABLELIST WHERE TableId=@FLGTAB
insert into @TABLESSIZE EXEC sp_spaceused @TABLENAME
SET @FLGTAB=@FLGTAB+1
END
SELECT * FROM @TABLESSIZE
SCHEMA_NAME(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows
from sys.tables t
INNER JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)order by t.NAME
2.
DECLARE @TABLESSIZE TABLE (TableId INT identity(1,1),TableName varchar(500),RowCounts int,Reserved varchar(50),Data varchar(50),index_size varchar(50),unused varchar(50))
DECLARE @TABLELIST TABLE (TableId INT identity(1,1),TableName varchar(500))
DECLARE @TABLENAME VARCHAR(500)
DECLARE @FLGTAB INT
DECLARE @CNTTAB INT
INSERT INTO @TABLELIST select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_NAME like 'TOP%'
SET @FLGTAB=1
SELECT @CNTTAB=COUNT(1) FROM @TABLELIST
WHILE(@FLGTAB<=@CNTTAB)
BEGIN
SELECT @TABLENAME=TableName from @TABLELIST WHERE TableId=@FLGTAB
insert into @TABLESSIZE EXEC sp_spaceused @TABLENAME
SET @FLGTAB=@FLGTAB+1
END
SELECT * FROM @TABLESSIZE
