Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.13 seconds. Powered By: Snitz Forums 2000