| Downloads |
|---|
| 5236.zip |
You can effectively obtain record counts for tables by querying the sysindexes table for an index ID of 0 or 1. However, I’d like to present a solution that uses better syntax, and I’d like to discuss some considerations if you use SQL Server 7.0. A home grown utility I’ve been using retrieves record counts better than the SQL Server 6.5 method of querying for an index ID of 0 or 1. Because no index will have more entries than the number of rows, I simply choose max(rows).
I include this procedure in all my databases to make it available to the developers. and even the application. Users I find that some users also like to see an alphabetical list of the total record counts for their tables. In addition I provide the ability to identify the object owner. I think you will find the code in Listing 1 useful.
Although this syntax functions properly in both SQL Server 6.5 and 7.0, this approach is incorrect in version 7.0. The Microsoft direction uses the ANSI standard named INFORMATION_SCHEMA and doesn’t touch the system tables directly. Because Microsoft will make future changes to the system tables, using INFORMATION_SCHEMA protects your code by creating a level of abstraction between your code and the system tables. Eventually, this approach lets you build code to function identically on various DBMS platforms such as Oracle or DB2. However, as you can see from the code in Listing 2, this vision is not truly implemented yet.
I still refer to sysindexes to get the record counts because referring exclusively to the INFORMATION_SCHEMA objects is not possible. In fact, when generating scripts from Enterprise Manager, Microsoft still generates calls to sysobjects. So, we obviously have a way to go before reaching the utopia of database-independent system tables.
If you are curious how Enterprise Manager creates the bar graphs of space used and record counts, simply turn on the SQL Profiler and watch the database calls that Enterprise Manager makes. Enterprise Manager makes a call to sp_MStablespace for each table. (Microsoft might want to streamline this process because of the many database calls to accomplish this relatively simple task.)
By copying some of the contents of sp_Mstablespace, you can also accomplish this functionality in a view, as in Listing 3. The DataSpace column is the number of kilobytes (i.e., 8 = 8KB). This view works in both SQL Server 6.5 and 7.0, but I’ve tested it only in 7.0.
The biggest advantage to a view is the ability to apply the where and order by clauses as you prefer. For example, your top consumer of space may not necessarily be the table with the most records. The Northwind database is an example. Table 1 shows the Order Details table with the largest number of records, but this table does not use the most space.
Additionally, you can build this view to query the INFORMATION_SCHEMA. Listing 4 shows this solution.