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?
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.
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)`
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
select count(rownum) from studennt
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