Post reply

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

  • May 18, 2006 at 9:33 am

    #113447

    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

  • May 18, 2006 at 9:41 am

    #638779

    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.

  • May 18, 2006 at 9:46 am

    #638781

    Another (documented) option & rather fast...

    DBCC

    SHOWCONTIG WITH TABLERESULTS

  • May 18, 2006 at 9:50 am

    #638782

    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

  • May 18, 2006 at 10:22 am

    #638795

    Thanks! those did the trick 

    Ken

  • May 22, 2006 at 4:04 am

    #639183

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

    select O.name,

    rowcnt

    from sysindexes I join sysobjects O on O.id = I.id

    where indid < 2

    and O.type = 'U'

    order

    by O.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