August 19, 2011 at 1:45 am
Comments posted to this topic are about the item Total Data Rows Data
The Fastest Methods aren't always the Quickest Methods
August 19, 2011 at 8:27 am
Slightly easier: -
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = 'SELECT SUM(rn) FROM (' +
ISNULL(STUFF((SELECT ' UNION ALL SELECT COUNT(*) AS rn FROM ' + table_catalog+'.'+table_schema+'.'+table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_catalog=db_name() FOR XML PATH('')),1,10,''),'SELECT 0 AS rn') + ')a'
EXEC(@SQL)
August 21, 2011 at 12:39 pm
August 24, 2011 at 6:41 am
The downside to the methods described is that they require a query to be run on every table. The downside to my method below is that it uses the sys.sysindexes table, which may not be available in future versions of SQL Server. However, it uses the statistics on the indexes to find the row counts rather than having to query every table.
SELECT CAST(o.name AS VARCHAR(50)) AS TableName,
i.rows AS TableRows
FROM sys.sysobjects o
JOIN sys.sysindexes i
ON i.id = o.id
WHERE i.indid IN (0, 1)
AND o.type = 'U'
AND o.name NOT IN ('dtproperties', 'sysdiagrams')
ORDER BY o.name
COMPUTE SUM(i.rows)
May 12, 2016 at 7:19 am
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply