[sql-server] SQL count rows in a table

I need to send a SQL query to a database that tells me how many rows there are in a table. I could get all the rows in the table with a SELECT and then count them, but I don't like to do it this way. Is there some other way to ask the number of the rows in a table to the SQL server?

This question is related to sql-server count rows database-table

The answer is


Use This Query :

Select
    S.name + '.' + T.name As TableName ,
    SUM( P.rows ) As RowCont 

From sys.tables As T
    Inner Join sys.partitions As P On ( P.OBJECT_ID = T.OBJECT_ID )
    Inner Join sys.schemas As S On ( T.schema_id = S.schema_id )
Where
    ( T.is_ms_shipped = 0 )
    AND 
    ( P.index_id IN (1,0) )
    And
    ( T.type = 'U' )

Group By S.name , T.name 

Order By SUM( P.rows ) Desc

Here is the Query

select count(*) from tablename

or

select count(rownum) from studennt

select sum([rows])
from sys.partitions
where object_id=object_id('tablename')
 and index_id in (0,1)

is very fast but very rarely inaccurate.


The index statistics likely need to be current, but this will return the number of rows for all tables that are not MS_SHIPPED.

select o.name, i.rowcnt 
from sys.objects o join sys.sysindexes i 
on o.object_id = i.id
where o.is_ms_shipped = 0
and i.rowcnt > 0
order by o.name

Why don't you just right click on the table and then properties -> Storage and it would tell you the row count. You can use the below for row count in a view

SELECT SUM (row_count) 
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID('Transactions')    
AND (index_id=0 or index_id=1)`

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to count

Count the Number of Tables in a SQL Server Database SQL count rows in a table How to count the occurrence of certain item in an ndarray? Laravel Eloquent - distinct() and count() not working properly together How to count items in JSON data Powershell: count members of a AD group How to count how many values per level in a given factor? Count number of rows by group using dplyr C++ - how to find the length of an integer JPA COUNT with composite primary key query not working

Examples related to rows

SQL count rows in a table Converting rows into columns and columns into rows using R Delete rows containing specific strings in R How to append rows to an R data frame Excel Create Collapsible Indented Row Hierarchies Excel CSV. file with more than 1,048,576 rows of data Find which rows have different values for a given column in Teradata SQL More than 1 row in <Input type="textarea" /> Update multiple rows with different values in a single SQL query Repeat rows of a data.frame

Examples related to database-table

Count the Number of Tables in a SQL Server Database SQL count rows in a table Mysql: Select rows from a table that are not in another Import CSV to mysql table MySQL > Table doesn't exist. But it does (or it should) Create table in SQLite only if it doesn't exist already Copy a table from one database to another in Postgres Truncating all tables in a Postgres database Maximum number of records in a MySQL database table Why use multiple columns as primary keys (composite primary key)