SQL: Table Width
Author: jason
Date: 2014-01-31 13:13:49
Category: Musing

I ran across this method for calculating the width of a table. Not sure where I got it from though...

The script uses the SysColumns to SUM the Length field. Uncomment the HAVING clause to limit to the "big" tables. Also note that its neat to ORDER BY Position 2 in the SELECT statement instead of rewriting the SUM...




Here is a SELECT from SysColumns so we can see what the raw content look like.




Row-Overflow Data Exceeding 8 KB
http://technet.microsoft.com/en-us/library/ms186981(v=sql.105).aspx

Code

SELECT OBJECT_NAME(id) [Table], SUM(length) [Width]
FROM syscolumns WHERE
OBJECTPROPERTY(id,'IsTable') = 1
AND OBJECTPROPERTY(id,'IsMsShipped') = 0
GROUP BY OBJECT_NAME(id)
--HAVING SUM(length) > 8000 --<-- This is the Greater than 8K Pages apparently
ORDER BY 2 DESC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009