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
 General SQL Server Forums
 Script Library
 Table Row / DataSize Simple Stats

Author  Topic 

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-17 : 06:15:38
Here's a simple script for some statistics on your tables

SELECT
o.[name] AS [Table_Name]
, rc.[rows] AS [Data_Rows]
, rc.[rows] / CASE WHEN rc.[usedPages] = 0 THEN 1 ELSE CAST(rc.[usedPages] AS FLOAT) END AS [Rows_Per_Page]
, ic.[Indexes] AS [DeclaredIndexes]
, ROUND(rc.[usedPages] * 8 / 1024.0, 2) AS [Data_Size_MB]
, ROUND(ISNULL(ic.[usedPages] * 8, 0) / 1024.0, 2) AS [Index_size_MB]
FROM
sys.objects AS o

-- Row Counts
CROSS APPLY (
SELECT
SUM ([row_count]) AS [rows]
, SUM([used_page_count]) AS [usedPages]
FROM sys.dm_db_partition_stats AS ss
WHERE
ss.[object_id] = o.[object_Id]
AND ss.[index_id] IN (0, 1)
)
AS rc

-- Index Stats
OUTER APPLY (
SELECT
COUNT(*) AS [Indexes]
, SUM ([row_count]) AS [rows]
, SUM([used_page_count]) AS [usedPages]
FROM sys.dm_db_partition_stats AS ss
WHERE
ss.[object_id] = o.[object_Id]
AND ss.[index_id] > 1
)
AS ic
WHERE
o.[type] = 'U'
AND o.[is_ms_shipped] = 0
AND rc.[rows] > 0
ORDER BY
rc.[rows] DESC
, o.[name]


Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
   

- Advertisement -