December 19, 2012 at 12:15 pm
I use the following script and in some cases it return higher records counts than the number of records in the table.
I'm looking for a script that returns accurate information.
This is the current script that I have.
SELECT sc.name AS SchemaName, ta.name AS TableName
,SUM(pa.rows) RecordCount
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
GROUP BY sc.name,ta.name
ORDER BY ta.NAME
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2012 at 12:47 pm
Generally, for accurate counts, I'd run a COUNT(*) query. Assuming good indexes on the table, you're guaranteed a quick and accurate value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2012 at 1:11 pm
I use this:
SELECT s.name, o.name, SUM(ps.reserved_page_count * 8),
SUM(CASE WHEN ps.index_id < 2 THEN ps.row_count ELSE 0 END)
FROM sys.objects o
INNER JOIN sys.dm_db_partition_stats ps
ON o.object_id = ps.object_id
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
GROUP BY s.name, o.name
ORDER BY 1, 2'
Jared
CE - Microsoft
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply