Home
Browse Code Add-Ins Blog Language Feeds Service Stats FAQs Feedback About
Manage Your Code
Snippet: Calculate Table Row Size in SQL 2005 (SQL)
Title: Calculate Table Row Size in SQL 2005 Language: SQL
Description: This script can be used to help produce database size estimates. It takes the size of a table row and includes index sizes to give you an estimate of how much space a given row will take up. Views: 224
Author: Michael Wood Date Added: 5/2/2008
Copy Code   
SELECT a.name,sum(a.max_length)as Bytes
FROM (
            SELECT ST.NAME,SC.MAX_LENGTH
            FROM SYS.COLUMNS sc inner join sys.tables st on sc.object_id = st.object_id
            UNION ALL
            SELECT ST.NAME,SC.MAX_LENGTH
            FROM SYS.INDEXES SI INNER JOIN SYS.TABLES ST ON SI.OBJECT_ID = ST.OBJECT_ID
            inner join sys.columns sc on sc.object_id = st.object_id
            inner join sys.index_columns sic on sc.object_id = sic.object_id
            and sc.column_id = sic.column_id ) a
group by a.name
order by a.name
Usage 
Paste and execute
Notes
Multiply the bytes value by the number of estimated rows to get a relative size of the table. Provided by Chris Barth. Note that this isn't completely accurate in that it doesn't taken into account page splitting and assumes pages are full, etc.