Post reply

System table that hold No of rows on user tables??

  • May 20, 2005 at 2:42 am

    #86396

    Hi,

    In SQL Server is there are system table that holds the number of rows on all other tables.  In oracle it is very obvious (displayed in the GUI) but on SQL Server I can't seem to find anything.  I have a database with 700 tables, and need to get a list of all the tables that have records and all those that don't. 

    Any ideas?

    Thanks,

    Paula.

  • May 20, 2005 at 2:54 am

    #560122

    SELECT OBJECT_NAME(id) AS tablename, rowcnt FROM dbo.sysindexes WHERE indid IN (0, 1)

    This select statement will return the number of rows for every table. However, this rowcount is not guaranteed to always be correct, so you should not rely on it if this is crucial to the system. For simply listing the names of all tables that have rows you could use this:

    EXEC sp_MSforeachtable @command1 = 'IF EXISTS( SELECT * FROM ? ) PRINT ''?'''

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • May 20, 2005 at 5:52 am

    #560150

    That's great.  Thankyou!

Viewing 3 posts - 1 through 3 (of 3 total)

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