I have written following script every time when I am asked by our team leaders or managers that how many rows are there in any particular table or sometime I am even asked which table has highest number of rows. Being Sr. Project Manager, sometime I just write down following script myself rather than asking my developers.
This script will gives row number for every table in database.
USE AdventureWorks
GO
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDERÂ BY st.row_count DESC
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
41 Comments. Leave new
Followup: If you want to get the number of records for each table, you need to group by object ID and then sum for each group.
man u are ossom….
Can someone help me with my problem?
My table looks like this
CID FNAME MIN MAX COM
A OP1 0 23 5
A OP1 24 35 2
A OP1 36 99 1
A OP2 0 23 5
A OP2 24 35 2
A OP2 36 99 0
A OP3 0 23 5
A OP3 24 35 2
A OP3 36 99 1
B OP1 0 23 9
B OP1 24 99 2
B OP2 0 23 9
B OP2 24 99 2
B OP3 0 23 7
B OP3 24 35 3
B OP3 36 99 1
The expected duplicate results i need are:
A OP1
A OP3
B OP1
B OP2
thanks
how can i get every table rows base on a snapshot? please help
HI Pinal,
You did good job.
Kommineni
Most of suggested solutions will work for sure but I suggest this:
EXEC sp_spaceused ‘Table_Name’
It gives some other useful information as well! Hope it helps :)
Just in case you want to get row counts for all tables in a database, you can run the following:
exec sp_MSforeachtable ‘exec sp_spaceused ”?”’
(all quotes are single quotes [‘])
Note that sp_MSforeachtable is undocumented stored procedure and you need to keep this in mind
Pinal, How to count column of a table in sql server?
Do you want to know total number of columns in a table? If so, use this
thanks for this information. explained well and quick
sir
i have assignment table in that table assigmnet table iam going to assign some requirements for all the recruters but iam going to assign multiple requirements for single one how to calculate how many records he have in that table
using sql server
An interesting discussion is worth comment. There’s no doubt that that you should publish more on this subject matter, it might not be a taboo subject but generally people do not discuss such issues.
To the next! Kind regards!!
Hi Pinal,
I have totally 1000 tables in a database . Now i have to count table wise row count that should be for 100 tables only . so how to write a query for that…
You can apply the filter like below
WHERE OBJECT_NAME(OBJECT_ID) in (‘table1′,’table2′,…..,’table100’)