| Downloads |
|---|
| 24516.zip |
Can I create a SQL query that lists the top 10 largest tables in a database and displays how many rows and total bytes each table uses?
The only 100 percent accurate way to check the number of rows in a table is to use a COUNT(*) operation such as the statement
SELECT COUNT(*) FROM MyTable
However, a COUNT() function won't give you the total number of bytes that the table uses. Also, the above statement might consume significant resources if your tables are very big because scanning a large table or index can consume a lot of I/O.
To get information such as the names of the tables that have the most rows, you can create a stored procedure that opens a cursor against all the table names in a database, then dynamically creates SQL statements to perform counts against all the tables. However, you can also get this type of information by directly querying the SQL Server system tables. Remember that Microsoft discourages this practice—I never directly query system tables from applications because I don't want my applications to stop working if the system tables change. But I make exceptions for ad hoc management purposes—and a report that shows me the 10 largest tables in a database falls into that category.
Each SQL Server database has a table called sysindexes that stores one row for each table and index in the database. SQL Server Books Online (BOL) documents the structure of sysindexes; the value of sysindexes.indid will always be 0 for a table and 1 for a clustered index. If a table doesn't have a clustered index, its entry in sysindexes will always have an indid value of 0. If a table does have a clustered index, its entry in sysindexes will always have an indid value of 1. Thus, the query in Listing 1 shows how many pages and rows each table in a database contains.
You can use OBJECTPROPERTY(), as Listing 1 shows, only on the user tables. The rowcnt column contains the number of data rows in the table, and the dpages column contains the number of data pages that the table uses. A SQL Server data page is 8KB, so you multiply dpages by 8 to get the size of a table in kilobytes.
Note that Microsoft doesn't guarantee that the dpages number and the row count from sysindexes are completely accurate because the data isn't all maintained in realtime. However, SQL Server 2000 especially does a good job of keeping these values up-to-date, and you can use the DBCC UPDATEUSAGE command to correct inaccuracies in the sysindexes table. The query in Listing 1 runs quickly and gives you an accurate estimate of how large your tables are. To determine the top 10 largest tables, you can easily add a TOP clause to the query, as Listing 2 shows.
A final note: SQL Server 2000 introduced the rowcnt column; earlier SQL Server releases use a column called rows. BOL says that the rows column still exists in SQL Server 2000, but for backward compatibility only, which is why I use the rowcnt column instead.