none
number of rows in tables RRS feed

Answers

  • SELECT

     

    OBJECT_NAME(object_id) from sys.partitions WHERE object_id IN (SELECT object_id FROM sys.tables) AND index_id IN (0,1) and rows > 10

     

     

     This should give you the rows for both heap and cluster tables. However bear in mind that if the table is partitioned then it will split and show the rowcount for each partition

     

     

     



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!

     

     

     
    • Proposed as answer by Naomi NModerator Wednesday, April 28, 2010 3:54 PM
    • Marked as answer by iGGt Thursday, April 29, 2010 12:15 PM
    Wednesday, April 28, 2010 3:48 PM
  • You can use the undocumented MSFOREACHTABLE to dump the result in a temp table then query it

    DECLARE @Temp TABLE (TableName VARCHAR(200),TableCount BIGINT) 
    INSERT @Temp 
    EXEC sp_MSForEachtable 'select ''?'',count(*) from ?' 
     
    SELECT * 
    FROM @Temp
    WHERE TableCount > 1000

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Naomi NModerator Wednesday, April 28, 2010 3:54 PM
    • Marked as answer by iGGt Thursday, April 29, 2010 12:15 PM
    Wednesday, April 28, 2010 3:48 PM

All replies

  • Wednesday, April 28, 2010 3:45 PM
  • SELECT

     

    OBJECT_NAME(object_id) from sys.partitions WHERE object_id IN (SELECT object_id FROM sys.tables) AND index_id IN (0,1) and rows > 10

     

     

     This should give you the rows for both heap and cluster tables. However bear in mind that if the table is partitioned then it will split and show the rowcount for each partition

     

     

     



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!

     

     

     
    • Proposed as answer by Naomi NModerator Wednesday, April 28, 2010 3:54 PM
    • Marked as answer by iGGt Thursday, April 29, 2010 12:15 PM
    Wednesday, April 28, 2010 3:48 PM
  • You can use the undocumented MSFOREACHTABLE to dump the result in a temp table then query it

    DECLARE @Temp TABLE (TableName VARCHAR(200),TableCount BIGINT) 
    INSERT @Temp 
    EXEC sp_MSForEachtable 'select ''?'',count(*) from ?' 
     
    SELECT * 
    FROM @Temp
    WHERE TableCount > 1000

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Naomi NModerator Wednesday, April 28, 2010 3:54 PM
    • Marked as answer by iGGt Thursday, April 29, 2010 12:15 PM
    Wednesday, April 28, 2010 3:48 PM
  • cheers guys.
    Thursday, April 29, 2010 12:15 PM