CODEWITHSUNDEEP
sql-server-2012rowcount
5tar-Kaster
5tar-Kaster

Reputation: 908

SQL to find the number of rows per table in a database

My goal is to create a spreadsheet of all the tables in the current database that will display the table name and the number of rows in each table. A bonus would be to also be able to display when anything was last entered or changed in the table. I'm not sure if the last part is possible though.

This is what I have so far... its nothing much but I just can't beat my head around how to add the per table row count to it.

SELECT name, type, type_desc
FROM [scheme].sys.all_objects
WHERE type = 'U'

Upvotes: 3

Views: 3417

Answers (2)

marc_s
marc_s

Reputation: 755381

You can use this query against the system catalog views:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

to get the desired result - all tables in the current database, and the number of rows in each.

Upvotes: 3

Amir Keshavarz
Amir Keshavarz

Reputation: 3118

CREATE TABLE #T
(TABLENAME VARCHAR(100) ,
COUNT INT)
INSERT INTO #T
EXEC SP_MSFOREACHTABLE 'SELECT ''?'' ,COUNT(*) FROM ?'
SELECT * FROM  #T
DROP TABLE #T

Upvotes: 1

Related Questions