Click here to Skip to main content
16,018,418 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using sql server 2005 database. I would like to know how can I calculate the size(kb/mb/gb) of each row of a database table.
Posted

1 solution

I didn't invent this (I'm not that good) - but I can't remember where I got it:
SQL
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 
It returns a dataset of table name and row size (approximate)
 
Share this answer
 
Comments
Shining Legend 22-Jun-11 0:33am    
Thanks for the solution. But I would like to know the size occupied by each row in the table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900