/*******************************************************************
*	Project:	I-Tech SQL Scripts
*	Keywords:	[Administration][Analysis][MetaData]
*	Name:		SIZING.AllTables.MaxRowLength.sql
*	Version:	1.1
*	Editions:	2005, 2008, 2008 R2, 2012
*	Prereqs:	
*	Copyright (C) 2012 I-Tech Solutions, Inc. under Microsoft Reciprocal License (MS-RL) http://www.microsoft.com/en-us/openness/licenses.aspx
*	
*	Author:			Date:			Version:		Summary:
*	Ben Johnston	2012-10-25		1.0				Created
*	Ben Johnston	2012-10-26		1.1				Updated to add HEAP overhead when needed.
*
*	Purpose:	Show the maximum possible row length for a table.
*
*	Notes:		Does not include columns with BLOB or XML data.
*				Set the following variables: 
*				@SCHEMA				Schema for the tables. Set to NULL to show size from all schemas.
*
********************************************************************/
SET NOCOUNT ON
GO

DECLARE @SCHEMA		varchar(255)
SELECT @SCHEMA		= NULL		--'dbo'

--Version accounting for HEAP RID
;
WITH INDEX_CTE (
	ixObjectID
	,ixIndexID
	,SELECT_CRITERIA
)
AS (
	SELECT
		so.ID
		,si.index_id
		,ROW_NUMBER() OVER(PARTITION BY so.ID ORDER BY so.ID, si.index_id) SELECT_CRITERIA
	FROM sys.sysobjects so
		LEFT JOIN sys.indexes si
			ON so.id	= si.object_id
)
,MAXROW_CTE AS (
	SELECT
		ss.name			maxSchemaName
		,so.name		maxObjectName
		,so.ID			maxObjectID
		,SUM(length) +
			CASE MAX(ixIndexID)
				WHEN 0 THEN 10		--HEAPs have 10 bytes of overhead (RID).
				ELSE 0
			END	TableRowMaxLength
	FROM sys.sysobjects so
		INNER JOIN sys.syscolumns sc
			ON so.id = sc.id
		INNER JOIN sys.schemas ss
			ON so.uid = ss.schema_id
		INNER JOIN INDEX_CTE
			ON ixObjectID = so.id
	WHERE SELECT_CRITERIA = 1
		AND length > 0				--XML, varbinary, etc. columns are listed as length = -1
	GROUP BY ss.name
		,so.name
		,so.ID
)


SELECT
	maxSchemaName
	,maxObjectName
	,TableRowMaxLength
FROM MAXROW_CTE
WHERE maxSchemaName = ISNULL(@SCHEMA,maxSchemaName)
ORDER BY maxSchemaName
	,maxObjectName
GO


/*
--Simple version
SELECT
	ss.name			SchemaName
	,so.name		ObjectName
	,SUM(length)	TableRowMaxLength
FROM sys.sysobjects so
	INNER JOIN sys.syscolumns sc
		ON so.id = sc.id
	INNER JOIN sys.schemas ss
		ON so.uid = ss.schema_id
--WHERE so.name		= 'TransactionalLoad'
--	AND ss.name		= 'dbo'
WHERE ss.name		= 'dbo'
GROUP BY ss.name
	,so.name
--ORDER BY colid
ORDER BY ss.name
	,so.name
*/