SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Table Row / DataSize Simple Stats
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/17/2012 :  06:15:38  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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.
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000