Priya

• Nov 27th, 2005

 

select segment_name from dba_segments where bytes/1024/1024<100;

Genie Cool

• Jun 6th, 2007

 

If you are trying SQL Server 2000, try the code that gives the size in bytes...

DECLARE @CalSize TABLE
(
 TabID INT,
 ColSize INT
)

INSERT INTO @CalSize
 SELECT A.id, SUM(A.Length) As Length
 FROM
  (Select Length, id from syscolumns) A
 INNER JOIN
  (select id from sysobjects where type = 'U') B
 ON
  A.id = B.id
 GROUP BY A.id

SELECT * FROM @CalSize

DECLARE @RowSize TABLE
(
 TabID INT,
 RowSize INT
)

INSERT INTO @RowSize
 SELECT A.id, A.Row
 FROM
  (SELECT SUM(ROWS) as Row, id FROM SYSINDEXES where indid = 0 GROUP BY ID) A
 INNER JOIN
  (SELECT Tabid from @calSize) B
 ON
  A.id = B.Tabid

Select * from @RowSize

SELECT C.name as TableName, (A.Colsize * B.RowSize) as 'Size'
 FROM
  @CalSize A
 INNER JOIN
  @RowSize B
 ON
  A.Tabid = B.Tabid
 Inner join
  SysObjects C
 On
  C.id = A.TabID