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