For
finding the number of records we generally use the count function. Here I am
using AdventureWorksDW2008R2
Database.
SELECT Count(*) As [Number
of Records]
FROM
[AdventureWorksDW2008R2].[dbo].[FactInternetSales]
|
For
finding the total number is use ROW scan due to that It will take much time.
See the execution plan.
In
this example there are only 60k records. Think if we have millions of record
that who much time it will be take.
We
can find the row count using below system views.
Using Catalog
View sys.partitions
It Contains a row for each partition of all the tables and most
types of indexes in the database. Special index types such as Full-Text, Spatial,
and XML are not included in this view. All tables and indexes in SQL Server
contain at least one partition, whether or not they are explicitly partitioned.
Here is the script to find the row count of all the tables using
sys.partitions.For finding the row count of the all table in the database use
below script
SELECT T.NAME AS
TableName
,SUM(P.ROWS) AS [RowCount]
FROM sys.partitions AS P
INNER JOIN sys.tables AS T
ON P.OBJECT_ID = T.OBJECT_ID
WHERE P.INDEX_ID < 2
GROUP BY T.NAME
ORDER BY [RowCount] DESC
|
If
we want to find the row count for the single table we need to filter that.
Using DMV
sys.dm_db_partition_stats
It returns page and row-count information for every partition in the
current database.
With the help of dm_db_Partitio_stats we can get the number of
records. See below script.
SELECT T.NAME AS
TableName
,SUM(S.ROW_COUNT) AS [RowCount]
FROM
sys.dm_db_partition_stats AS S
INNER JOIN sys.tables
AS T
ON S.OBJECT_ID =
T.OBJECT_ID
WHERE S.INDEX_ID <
2
GROUP BY T.NAME
ORDER BY [RowCount]
DESC
|
For finding the row count of single table we need to filter
that.
Get the Records count using sp_MSforeachtable SP
We will get the records count of the table in a database
using SP_MSforeach table.
use AdventureWorksDW2008R2
CREATE TABLE #TempRecordsCounts
([Table
Name] VARCHAR(200) ,
[Number of
records] INT)
EXEC sp_MSforeachtable 'insert into
#TempRecordsCounts select ''?'', count(*) from ?'
SELECT *
FROM #TempRecordsCounts
ORDER BY [Number of records] desc
DROP TABLE #TempRecordsCounts
|
For finding the row count of a single table
