Wednesday, March 06, 2013 7:19 PM
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?
Wednesday, March 06, 2013 7:31 PM
Several options here:
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
Wednesday, March 06, 2013 9:20 PMYes a variation of the code you provided did the trick for me. Thanks.