SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

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

It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Above query will return following resultset.

Read more: Journey to SQL Authority with Pinal Dave Part 1, Part 2

Posted via email from .NET Info