Archive for the ‘MSSQL’ Category

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

Tuesday, May 15th, 2007 
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

-----------------------------
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

Posted in MSSQL | No Comments »