to calculate the row size of the table in sqlserver ?

• Bhanu Mathi-313172

  SSC Rookie

  Points: 25

  More actions

  April 24, 2006 at 3:27 pm

  #167857

  Hello all, This is my first time here I am posting my question. Thanks again in advance ,to all for this wonderful help.

  I have some 700 tables in sqlserver database. I would like to know the complete rowsize for each these 700 tables in the database assuming there are approximately some 100 records in each table. Is there any query available.

  The  understand that usually we calcultae the rowsize as the entire length of the complete row(length size) multiplied by the number of rows..but for the project I am working now, I do not have the proper idea about how much data eventually this database can hold and hence would like to know how to calculate the lenght of each table with ONE row or hundred rows etc etc.

  Hope I ma not confusing with my question..what I am looking is..for example..

  let us say we have two tables..table1 with col1 varchar2(10), col2 varchar2(20),

  table2 with col1 as varchar2(2), and col2 as varchar2(2).

  I would like to have a query which after running for a table..gives me table1= 30char size and table2 = 2+ 2= 4charsize.

  Is this possible ?

  Please let me know and it woudl be great help.

   

  Thanks again in advance.

  Regards,

  Bhanu

   

   

   

   

• Lowell

  SSC Guru

  Points: 323546

  More actions

  April 25, 2006 at 6:46 am

  #633990

  i THINK this sql will list all tables defined row size:  the LENGTH column in syscolumns has the #of bytes used for the column definition; a varchar(30) has 30, nvarchar(30) has 60, where an int has 4 bytes; let us know if this works for you.

   

  create table ##tmp (TableName varchar(40),DefinedRowSize int)

  sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '

  select * from ##tmp order by DefinedRowSize desc

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  April 25, 2006 at 11:47 am

  #634132

  If a table exists, use sp_spaceused.

  If a tabe does not, then you need to include additional factors that take into account ... does the table have a clustered index or not .. is a column is varchar or not and whether or not a column is nullable. Fior varchar columns, att one additional byte ber column, for nullable columns its a bit map (basically 1 byte plus whatever bits rounded up to the next byte in size). Check out BOL for:

  Estimating the Size of a Table Without a Clustered Index

  Estimating the Size of a Table With a Clustered Index

  It might seem trivial but those little bits add up when tables scale from 100,000 rows to 10,000,000 rows ..

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."