--**************************************
--     
-- Name: qStats_Ex
-- Description:This a rewrite of qStats,
--      which was a rewrite of sp_TStats.
-- By: Ewald Hofman
--
-- Inputs:@TableName --> name of the 
--     table, and it must be a table, sorry, no
--      wild cards allowed (too lazy to use lik
--     e instead of =). No value will return al
--     l tables.
@Owner --> same AS above, but WITH the TABLE owners username.
--
-- Returns:Space utilization data for ta
--     bles
NAME --table name
NumRows --Number OF rows IN the TABLE
Data(kb) --spaced used IN kilobytes FOR the data
Index(kb) --spaced used in kilobytes FOR the indexes
Reserved(kb) --spaced used IN kilobytes reserved by the TABLE
Unused(kb) --space allocated but unused BY the table.
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=785&lngWId;=5--for details.--**************************************
--     

IF EXISTS (SELECT * FROM sysObjects WHERE Id = OBJECT_ID('dbo.qStats_Ex')) DROP PROCEDURE dbo.qStats_Ex
GO
CREATE PROCEDURE dbo.qStats_Ex
	@TableName	Varchar(30) = NULL
,	@Owner		Varchar(30) = NULL
AS
	SELECT
		Name		= sysObjects.Name
	,	NumRows		= CONVERT(Int, 
					SUM(	 ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1) THEN sysIndexes.Rows ELSE 0 END		, 0)
					)
				 )
	,	[Data (kb)]	= CONVERT(Int, 
					SUM(	 ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1) THEN sysIndexes.DPages ELSE 0 END		, 0)
						+ ISNULL(CASE WHEN sysIndexes.IndId IN (255) THEN sysIndexes.Used ELSE 0 END		, 0)
					)
					* spt_Values.Low / 1024
				 )
	,	[Index (kb)]	= CONVERT(Int, 
					SUM(	 ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1) THEN sysIndexes.Used ELSE 0 END		, 0)
						- ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1) THEN sysIndexes.DPages ELSE 0 END		, 0)
					)
					* spt_Values.Low / 1024
				 )
	,	[Reserved (kb)]	= CONVERT(Int, 
					SUM(	 ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1, 255) THEN sysIndexes.Reserved ELSE 0 END	, 0)
					)
					* spt_Values.Low / 1024
				 )
	,	[Unused (kb)]	= CONVERT(Int, 
					SUM(	 ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1, 255) THEN sysIndexes.Reserved ELSE 0 END	, 0)
						- ISNULL(CASE WHEN sysIndexes.IndId IN (0, 1, 255) THEN sysIndexes.Used ELSE 0 END	, 0)
					)
					* spt_Values.Low / 1024
				 )
	FROM
				sysObjects
		INNER JOIN	sysIndexes
					ON	sysIndexes.Id		= sysObjects.Id
		INNER JOIN	master.dbo.spt_Values		spt_Values
					ON	spt_Values.Name		= 'WINDOWS/NT'
					AND	spt_Values.Type		= 'E'
	WHERE
		sysObjects.Name = ISNULL(@TableName, sysObjects.Name)
	AND	sysObjects.Uid = ISNULL(USER_ID(@Owner), sysObjects.Uid)
	AND	sysObjects.Type = 'U'
	GROUP BY
		sysObjects.Name
	,	spt_Values.Low
GO