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.
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
Reference : Pinal Dave (https://blog.sqlauthority.com)
Dave Pinal, you REALLY should update the main article to use something like what Books Online shows:
SELECT SUM (row_count) AS total_number_of_rows
WHERE object_id=OBJECT_ID(‘HumanResources.Employee’) AND (index_id=0 or index_id=1);
That query handles multiple partitions, and handles the schema name correctly. It will show the results for a heap, or a table with a primary key. This has been mentioned in the comments a few times, and you say “thanks”, but it would help if you could update the article itself! The code in the article is not quite correct.
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:
how can i get every table rows base on a snapshot? please help
You did good job.
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
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!!
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’)