Master Station Log
techno-rants, musings, misc geek writing, and occasional caffeine binges
SQL: Table Width
Date: 2014-01-31 13:13:49
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
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