This Coding is used to count all the table records in tablewise and count the records in overall Database.
It's Simple query to count the records in each table.
--Specify the name of the database to count in the following line
USE master;
GO
SELECT sysobj.name AS "Table Name", sysind.rowcnt AS "Row Count"
FROM sysobjects sysobj, sysindexes sysind
WHERE sysobj.id = sysind.id
AND indid IN(0,1)
--This specifies 'user' databases only
AND xtype = 'u'
--This omits the diagrams table of the database
--You may find other system tables will need to be ommitted,
--you would just name them all here using the <> operator
--i.e. o.name <> dtproperties, o.name <> 'sysdiagrams'
AND sysobj.name <> 'sysdiagrams'
--You could also look further into filtering out temp tables,
--or user specified tables
ORDER BY sysind.rowcnt DESC
--the results by 'Row Count' Descending
COMPUTE SUM(sysind.rowcnt);
--overall count of the database
GO
|
| Author: Prashant Mishra 17 Sep 2009 | Member Level: Bronze Points : 2 |
Nice one:)
Also you can try try this on.
this is availble on dotnet spider "Tables' name with record count - SQL Server" .
CREATE TABLE #Table_Count
(
TableName VARCHAR(MAX),
RecordCount BIGINT
)
GO
INSERT #Table_Count
EXEC SP_MsForEachTable 'SELECT ''?'', COUNT(1) FROM ?'
GO
SELECT * FROM #Table_Count WHERE RecordCount > 0 ORDER BY RecordCount DESC
GO
DROP TABLE #Table_Count
|
| Author: Mohan 17 Sep 2009 | Member Level: Diamond Points : 2 |
Hi Prashant,
Thanks for sharing your knowledge with this resource.
I found one performance issue with your given SP.
My Database contains nearly 650 tables.
My query returns the records within 20 seconds. But Your given SP it takes 10 mins for completion.
So Better you dont follow that SP..
|