/******************************************************************* * 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 */