[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


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.


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

or

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

Questions with sql-server tag:

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 SQL Server IF EXISTS THEN 1 ELSE 2 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to add a boolean datatype column to an existing table in sql? How to import an Excel file into SQL Server? How to use the COLLATE in a JOIN in SQL Server? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Drop view if exists Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to update large table with millions of rows in SQL Server? Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers Select Rows with id having even number A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) SQL Server: Error converting data type nvarchar to numeric How to add LocalDB to Visual Studio 2015 Community's SQL Server Object Explorer? Using DISTINCT along with GROUP BY in SQL Server Rebuild all indexes in a Database How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio? The target principal name is incorrect. Cannot generate SSPI context How Stuff and 'For Xml Path' work in SQL Server? How to view the roles and permissions granted to any database user in Azure SQL server instance? How do I create a local database inside of Microsoft SQL Server 2014? Format number as percent in MS SQL Server MSSQL Regular expression How to select all the columns of a table except one column? SQL count rows in a table EXEC sp_executesql with multiple parameters SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Remove decimal values using SQL query How to drop all tables from a database with one SQL query? How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server Using Excel VBA to run SQL query No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server How to join two tables by multiple columns in SQL? The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with count tag:

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 Count distinct value pairs in multiple columns in SQL Fastest way to determine if record exists SQL Query with Join, Count and Where pandas python how to count the number of records or rows in a dataframe Javascript counting number of objects in object Sum values in foreach loop php How to obtain the total numbers of rows from a CSV file in Python? Pandas count(distinct) equivalent In Firebase, is there a way to get the number of children of a node without loading all the node data? Get number of digits with JavaScript Multiple aggregate functions in HAVING clause count distinct values in spreadsheet Oracle row count of table by count(*) vs NUM_ROWS from DBA_TABLES How do I count occurrence of duplicate items in array Count number of occurrences for each unique value How can I divide one column of a data frame through another? SUM of grouped COUNT in SQL Query Count how many files in directory PHP How to get multiple counts with one SQL query? Count multiple columns with group by in one query GROUP BY and COUNT in PostgreSQL PHP - count specific array values SQL to Entity Framework Count Group-By postgresql COUNT(DISTINCT ...) very slow Count with IF condition in MySQL query Extend contigency table with proportions (percentages) Count indexes using "for" in Python How to count number of records per day? COUNT / GROUP BY with active record? How to count the number of true elements in a NumPy bool array How to count the number of occurrences of a character in an Oracle varchar value? Java count occurrence of each item in an array How to count check-boxes using jQuery? Fast way to discover the row count of a table in PostgreSQL Trying to get the average of a count resultset Count number of rows per group and add result to original data frame jQuery count number of divs with a certain class? SQL query for finding records where count > 1 SQL: How to get the count of each distinct value in a column? Calculate average in java

Questions with rows tag:

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 Adding rows to tbody of a table using jQuery How to remove rows with any zero value Select multiple rows with the same value(s) Removing specific rows from a dataframe Converting Columns into rows with their respective data in sql server Add some word to all or some rows in Excel? Numpy - add row to array Compare two data.frames to find the rows in data.frame 1 that are not present in data.frame 2 Select only rows if its value in a particular column is less than the value in the other column Python: Number of rows affected by cursor.execute("SELECT ...) For each row in an R dataframe Convert multiple rows into one with comma as separator

Questions with database-table tag:

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) How do you find the row count for all your tables in Postgres Saving changes after table edit in SQL Server Management Studio SQL DROP TABLE foreign key constraint PostgreSQL create table if not exists Create table variable in MySQL Facebook database design? Table-level backup How do I specify unique constraint for multiple columns in MySQL? What is the difference between a schema and a table and a database? How can I create a copy of an Oracle table without copying the data? Search All Fields In All Tables For A Specific Value (Oracle) Copy tables from one database to another in SQL Server How do I get list of all tables in a database using TSQL?