Count Records in Each Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count Records in Each Table

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2011-03-16 : 18:48:03

Is it possible by script to return the record count and table name for each table in a database? If so how?

TIA

Education is what you have after you've forgotten everything you learned in school

doco
Yak Posting Veteran

77 Posts

Posted - 2011-03-16 : 19:17:08

Never mind - I found it.



USE AwbreyWheelerAsrdta	-- this name of course
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))

SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT 
a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM 
#temp a INNER JOIN 
information_schema.columns b ON 
a.table_name collate database_default = b.table_name collate database_default
GROUP BY 
--a.table_name, 
a.row_count, 
a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Education is what you have after you've forgotten everything you learned in school

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-03-17 : 05:38:21

Hi!
Just try this.....
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY o.NAME

sathish

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-17 : 07:50:34

I use this

SELECT	distinct schema_name(t.schema_id) [schema], t.name, p.rows
FROM	sys.tables t
JOIN	sys.partitions p
On	p.object_id = t.object_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources