Siusic Dot Com

A query to display all user tables in a database and sort them by decending order according to row counts is very useful when it comes to research and

discover where data are stored in a complex database. I found such query very helpful from time to time. I have collected a few of them here.

For SQL server 2000:

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = ‘U’
AND
si.id = OBJECT_ID(user_name(uid)+’.'+so.name)
GROUP BY
so.name
ORDER BY
2 DESC

For SQL Server 2005

–SQL 2005
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sys.tables so,
sys.sysindexes si
WHERE
so.type = ‘U’
AND
si.id = OBJECT_ID(schema_name(schema_id)+’.'+so.name)
GROUP BY
so.name
ORDER BY
2 DESC

SELECT OBJECT_NAME(st.OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
join sys.objects tb on st.object_id=tb.object_id
and objectproperty(tb.object_id, ‘isUserTable’)=1
WHERE st.index_id < 2
and st.row_count>0
ORDER BY st.row_count DESC

Related Posts:

MySQL Bugs

Force T-SQL Query to Use Certain Index to Improve Query Performance

The Ultimate Strategy of Tuning Store Procedure Performance

A Useful Script to Get The Disk Space Used by Every Table

Error 1222 Lock Request Time Out Period Exceeded When Set up Replication