name of table & count of rows 1

[rk68]

Hi,

I have around 400+ tables in MS-SQL.
Need to know the table name with number of rows of each table. Doing this individually will be tedious.
Is there a query thru which i can get the table name & no. of rows.
I tried some of the queries given in certain websites but didn't work. One of them I tried which was given in this forum, as given below
but the Cnts table was blank. Do I need to give any parameter in the stored procedure sp_MSForEachTable ?

CREATE TABLE ##Cnts (
Table_name varchar(100) NOT NULL
, Cnt int NOT NULL
)

sp_MSForEachTable 'INSERT ##Cnts SELECT '?' AS Table_Name, COUNT(*) Cnt FROM ?'

SELECT * FROM ##Cnts

TIA,
RAJ

 

[bborissov]

Based on this:

http://stackoverflow.com/questions/...nt-exact-number-of-rows-in-a-very-large-table

SELECT  object_name(object_id) AS TableName, SUM(st.row_count) AS Total_Rows
FROM   sys.dm_db_partition_stats st
WHERE (index_id < 2)
GROUP BY object_name(object_id)

Borislav Borissov
VFP9 SP2, SQL Server

 

[djj55]

Here is a query that gives more info about the tables:

SELECT  
    t.NAME AS TableName, 
    p.rows AS RowCounts, 
    SUM(a.total_pages) * 8 AS TotalSpaceKB,  
    SUM(a.used_pages) * 8 AS UsedSpaceKB,  
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
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 
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
WHERE 1 = 1 
    AND t.is_ms_shipped = 0 
GROUP BY t.Name, p.Rows 
ORDER BY UsedSpaceKB, t.Name

Or to get just what you asked:

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 1 = 1 
    AND t.is_ms_shipped = 0 
GROUP BY t.Name, p.Rows 
ORDER BY UsedSpaceKB, t.Name

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!