none
Row Counts On Certain Tables RRS feed

  • Question

  • I wish to get row counts for certain tables in a Database in a SQL Server 2005 Database.

    I have the following query that will get my tables that I need to get row counts for,
    the query returns the names of 11 Tables that i require row counts for.

    SELECT Article
    FROM Distribution..MSArticles
    WHERE Publisher_ID = 0

    I know how to return row counts for an entire Database, but how do I do it for just the
    11 Tables that the above query returns?

    Tuesday, July 12, 2011 3:55 AM

Answers

  •  

    declare @SQL nvarchar(max)
    
    select @SQL = coalesce(@SQL + '
    
    UNION ALL','') + 'SELECT ' + quotename(Article,'''') + ' as TableName, COUNT(*) As RecCount FROM ' + QUOTENAME(Article)
    
    FROM Distribution..MSArticles
    
    WHERE Publisher_ID = 0
    
    print @SQL
    
    EXECUTE (@SQL)
    

     

    The above is from the top of my head - may need a bit of tweaking.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, July 12, 2011 3:59 AM
    Moderator

All replies

  •  

    declare @SQL nvarchar(max)
    
    select @SQL = coalesce(@SQL + '
    
    UNION ALL','') + 'SELECT ' + quotename(Article,'''') + ' as TableName, COUNT(*) As RecCount FROM ' + QUOTENAME(Article)
    
    FROM Distribution..MSArticles
    
    WHERE Publisher_ID = 0
    
    print @SQL
    
    EXECUTE (@SQL)
    

     

    The above is from the top of my head - may need a bit of tweaking.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, July 12, 2011 3:59 AM
    Moderator
  • Hi Naomi,

    Thanks for the quick reply.

    I only had to change a spelling in your solution, in your FROM Clause it should be Distribution.

    I did that and it works, thanks you for that.

    Tuesday, July 12, 2011 4:08 AM
  • Hi FogHorn I,
    Can you please mark as answer?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Tuesday, July 12, 2011 5:37 AM
    Moderator