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?

6 Answers

Yes, SELECT COUNT(*) FROM TableName

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.

Here is the Query

select count(*) from tablename 

or

select count(rownum) from studennt 

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 

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. Use your table's name in the WHERE clause.

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

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