//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: How do I get a list of SQL Server tables and their row counts?


How do I get a list of SQL Server tables and their row counts?

Obvious methods 
 
The most straightforward method for determining the number of rows in a table, is to use the following: 
 
SELECT COUNT(*) FROM tablename
 
You can also use the system stored procedure, sp_spaceused, to determine other information, such as data and index size: 
 
EXEC sp_spaceused 'tablename'
 
To get an *approximate* count for all tables, you can use the following: 
 
SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC
 
The sysindexes table is usually a little bit inaccurate, because it is not updated constantly. It will also include the 'dtproperties' table, which is one of those hybrid tables that falls neither under the 'system' nor 'user' category. It does not appear in Enterprise Manager's "Tables" view if you choose to hide system objects, but it shows up above. 
 
In any case, it is generally not recommended to query against the system objects directly, so please only use the above for rough, ad-hoc guesstimates. 
 
Undocumented methods 
 
Please don't rely on these methods, or use them in production code. Undocumented stored procedures may change or be disabled in a future release, or even a service pack / hotfix; or, they could disappear altogether.  
 
The following creates your own diagnostic page to give you a quick overview of how many rows are in each table in a specific database. It uses my favorite of the undocumented, do-not-use-in-production system stored procedures, sp_MSForEachTable: 
 
CREATE PROCEDURE dbo.listTableRowCounts 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE @SQL VARCHAR(255) 
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
    EXEC(@SQL) 
 
    CREATE TABLE #foo 
    ( 
        tablename VARCHAR(255), 
        rc INT 
    ) 
     
    INSERT #foo 
        EXEC sp_msForEachTable 
            'SELECT PARSENAME(''?'', 1), 
            COUNT(*) FROM ?' 
 
    SELECT tablename, rc 
        FROM #foo 
        ORDER BY rc DESC 
 
    DROP TABLE #foo 
END
 
(The only reason a #temp table is used here is because we want the results ordered by largest row counts first. If the order can be arbitrary, you can just run the EXEC by itself.) 
 
If you want to run it from ASP, you can call it as follows: 
 
<% 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<connection_string>" 
    set rs = conn.execute("EXEC dbo.listTableRowCounts") 
    if not rs.eof then 
 
        response.write "<table><tr>" & _ 
            "<th>Table name</th>" & _ 
            "<th>Rows</th></tr>" 
 
        do while not rs.eof 
            response.write "<tr>" & _ 
                " <td>" & rs(0) & "</td>" & _ 
                " <td>" & rs(1) & "</td>" & _ 
                "</tr>" 
            rs.movenext 
        loop 
 
        response.write "</table>" 
    end if 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
%>
 
Note that this will only count USER tables, not system tables. You could consider creating this procedure in the master database and marking it as a system object; this way, you could execute it within the context of any database, instead of having to create a copy of the proc for each database. 
 
Replicating 'Taskpad / Table Info' view 
 
Several people have asked how to mimic what taskpad view in Enterprise Manager does for Table Info, without having to scroll or search to find tables, and without listing all of the (largely superfluous and mostly built-in) index names. This view shows all the tables, rowcounts, reserved size and index size. Here is a stored procedure that does it one better... it essentially fires an sp_spaceused (which includes data and free space, in addition to reserved and index size). Now, before you use it, please exercise caution. This relies on system tables, and the undocumented sp_msForEachTable. Its behavior may change between versions and service packs, so don't rely on it for production code. 
 
CREATE PROCEDURE dbo.allTables_SpaceUsed 
AS 
BEGIN 
    SET NOCOUNT ON      
 
    DBCC UPDATEUSAGE(0) 
 
    CREATE TABLE #t 
    ( 
        id INT, 
        TableName VARCHAR(32), 
        NRows INT, 
        Reserved FLOAT, 
        TableSize FLOAT, 
        IndexSize FLOAT, 
        FreeSpace FLOAT 
    ) 
 
    INSERT #t EXEC sp_msForEachTable 'SELECT 
        OBJECT_ID(PARSENAME(''?'',1)), 
        PARSENAME(''?'',1), 
        COUNT(*),0,0,0,0 FROM ?' 
 
    DECLARE @low INT 
 
    SELECT @low = [low] FROM master.dbo.spt_values 
        WHERE number = 1 
        AND type = 'E' 
 
    UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM 
        (SELECT id, r = SUM(si.reserved), i = SUM(si.used) 
        FROM sysindexes si 
        WHERE si.indid IN (0, 1, 255) 
        GROUP BY id) x 
        WHERE x.id = #t.id 
 
    UPDATE #t SET TableSize = (SELECT SUM(si.dpages) 
        FROM sysindexes si 
        WHERE si.indid < 2 
        AND si.id = #t.id) 
 
    UPDATE #t SET TableSize = TableSize + 
        (SELECT COALESCE(SUM(used), 0) 
        FROM sysindexes si 
        WHERE si.indid = 255 
        AND si.id = #t.id) 
 
    UPDATE #t SET FreeSpace = Reserved - IndexSize 
 
    UPDATE #t SET IndexSize = IndexSize - TableSize 
 
    SELECT 
        tablename, 
        nrows, 
        Reserved = LTRIM(STR( 
            reserved * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        DataSize = LTRIM(STR( 
            tablesize * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        IndexSize = LTRIM(STR( 
            indexSize * @low / 1024.,15,0) + 
            ' ' + 'KB'), 
        FreeSpace = LTRIM(STR( 
            freeSpace * @low / 1024.,15,0) + 
            ' ' + 'KB') 
        FROM #t 
        ORDER BY 1 
 
    DROP TABLE #t 
END

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?

 

 


Created: 12/10/2002 | Last Updated: 1/25/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (161)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...