Need to find the number of rows in every table in a DB

  • Is there a SQL command that will let me find the number rows of every table in a SQL server database? In other words a command or sp that when run would output the number of rows in every table in one go. I have a SQL database that has gotten rather large and would like to figure out which table is using all of the space without having to go through Enterprise Manager or QA and look at them individually.

    Thanks!

    Ken

  • You can use an undocumented procedure

    sp_msforeachtable 'SELECT ''?'',COUNT(*) FROM ?'

    also you could create a temp table and insert the results to it

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Another (documented) option & rather fast...

    DBCC

    SHOWCONTIG WITH TABLERESULTS

  • Run this through QA, output to File.  Open the file in QA and run.  Or, output to text and cut/paste into new QA window and run.

    set nocount on

    SELECT 'CREATE TABLE #tmpTableCount (TableName varchar(50),TableCount int)'

    SELECT 'INSERT INTO #tmpTableCount (TableName, TableCount) SELECT ''' + [Name] + ''' ,COUNT(*) FROM ' + [Name]

    FROM SYSOBJECTS

    WHERE xtype = 'U'

    SELECT 'SELECT TableName, TableCount FROM #tmpTableCount'

    SELECT 'DROP TABLE #tmpTableCount'

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Thanks! those did the trick 

    Ken

  • If an approximatve value is enough for you then this would give you what you want:

    selectO.name,

    rowcnt

    fromsysindexes I join sysobjects O on O.id = I.id

    whereindid < 2

    andO.type = 'U'

    order

    byO.name

    You can improve the exactness by running dbcc updateusage (0) with count_rows



    Bye
    Gabor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply