i have a task to cleanup the database from useless records. in the planning, first i have to check what are the tables that holds the most number of records. i know i can check them one by one manually, but the table list is too long and am thinking it's not too wise to run through them manually before checking if there is any automatic query that can do the job. manually, i can query each table using this query:
select count(*) from <table_name>
even using sysobjects, i could not find the current Number Of Records
select * from sysobjects s where type='U' and name = '<table_name>'
anybody has an idea?
An approximation for the number of rows in each table is kept as part of index statistics, and is stored in the yourDB..systabstats Assuming you run update statistics on a regular basis, here's how you can get the information.
SELECT o.name, t.rowcnt
FROM sysobjects o, systabstats t
WHERE o.id = t.id
AND t.rowcnt > 0 --ignore 0 row entries
AND o.name not like "sys%" --exclude system tables
ORDER BY t.rowcnt DESC