Home
Browse Code Add-Ins Blog Language Feeds Service Stats FAQs Feedback About
Manage Your Code
Snippet: Calculate Table Row Size in SQL 2000 (SQL)
Title: Calculate Table Row Size in SQL 2000 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: 116
Author: Michael Wood Date Added: 5/2/2008
Copy Code   
SELECT a.name,sum(a.length) as Bytes
FROM (
            SELECT st.name, sc.length 
            FROM syscolumns sc inner join sysobjects st on sc.id = st.id and st.type = 'U'
            UNION ALL
            SELECT st.Name,sc.length
            FROM sysindexes si 
            INNER JOIN sysobjects st ON si.id = st.id 
                                    and st.type = 'U'
            INNER JOIN sysindexkeys sk ON si.indid = sk.indid
                                      AND st.id = sk.id
            inner join syscolumns sc on sk.colid = sc.colid
                                     AND sc.id = st.id            
             ) a
group by a.name
order by a.name
Usage 
Paste and execute.
Notes
Multiply the size by the number of estimated rows to get the size of a table in bytes. 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.