Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query that captures the count for all tables in given database

Answered Query that captures the count for all tables in given database

  • Wednesday, March 06, 2013 7:19 PM
     
      Has Code
    Sign In to Vote
    0

    I am looking for a way to find the count of multiple tables using a single query.  The crux of what I want is in this format:

    SELECT name AS table_name
	,(SELECT COUNT(*) FROM sys.tables.name) AS row_count
	FROM sys.tables

    Obviously the sys.tables.name column needs to be a table variable, but I don't know how to go about it.  Anyone have a solution that doesn't involve cursors?
     

All Replies

  • Wednesday, March 06, 2013 7:31 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Several options here:

    http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

    This one is pretty fast:

    SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

    Chuck Pedretti | Magenic – North Region | magenic.com


    • Edited by Chuck Pedretti Wednesday, March 06, 2013 7:32 PM
    • Marked As Answer by SD Eric Wednesday, March 06, 2013 9:19 PM
    •  
     
  • Wednesday, March 06, 2013 9:20 PM
     
     
    Sign In to Vote
    0
    Yes a variation of the code you provided did the trick for me.  Thanks.