table size in database

• basketcasing

  Mr or Mrs. 500

  Points: 509

  More actions

  April 14, 2005 at 4:56 am

  #165083

  hi..

  how can i check the disk space used for tables in a SQL Server database? please help...

  thanks.

• Meghana

  SSC Eights!

  Points: 860

  More actions

  April 14, 2005 at 7:16 am

  #552677

  if it is for a single table you can use this

  sp_spaceused <tablename>

  DBCC SQLPERF(LOGSPACE)

  GO

   

  DBCC SQLPERF(Logspace) can be used to monitor the amount of space used and indicates when to back up or truncate the transaction log.

  Regards

  Meghana

  ---

  Regards,

  Meghana
  

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  April 14, 2005 at 9:33 am

  #552754

  Here is quick script that might help as well.  It will look up all User Tables for monitoring. 

   

  SET NOCOUNT ON

  CREATE TABLE #TableName( RowID int IDENTITY (1, 1) NOT NULL,

   TableName varchar(50) )

  INSERT INTO #TableName( TableName) 

  SELECT name AS TableName

  FROM sysobjects

  WHERE xtype IN( 'U', 'S') -- not 'S or system table

    AND name NOT LIKE 'sys%'

    AND UID = 1.0 -- Indicates a dbo created table

  CREATE TABLE #TableInformation(

   TableName nvarchar(50),

   RowsCount char(20),

   SpaceReserved varchar(25),

   SpaceUsed varchar(25),

   IndexSize varchar(25),

   SpaceUnused varchar(25))

  DECLARE @sql nvarchar(2000),

   @TableName varchar(100),

   @CurrentID integer,

   @MaxID integer

  SELECT @CurrentID = (SELECT MIN( RowID) FROM #TableName), 

   @MaxID = (SELECT MAX( RowID) FROM #TableName)

  WHILE @CurrentID <= @MaxID

  BEGIN

   SELECT @TableName = (SELECT TableName FROM #TableName

      WHERE RowID = @CurrentID)

   IF ISNULL( @TableName, 'N/A') <> 'N/A'

   BEGIN

    INSERT INTO #TableInformation

    EXEC sp_spaceused @TableName --, @updateusage = 'TRUE' -- when the update statics parameter is run, no records returned 

   END

   SELECT @CurrentID = (SELECT MIN( RowID) FROM #TableName WHERE RowID > @CurrentID)

  END

  SELECT TableName AS 'Table Name', CONVERT( integer, REPLACE( SpaceUsed, ' KB', '')) AS 'Space Used KB',

   CONVERT( integer, REPLACE( SpaceReserved, ' KB', '')) AS 'Space Reserved KB',

   CONVERT( integer, REPLACE( SpaceUnused, ' KB', '')) AS 'Space Unused KB',

   CONVERT( integer, RowsCount) AS 'Rows Count', CONVERT( integer, REPLACE( IndexSize, ' KB', '')) AS 'Index Size KB'

  FROM #TableInformation

  ORDER BY TableName ASC

  DROP TABLE #TableName

  DROP TABLE #TableInformation

  I wasn't born stupid - I had to study.

• basketcasing

  Mr or Mrs. 500

  Points: 509

  More actions

  April 15, 2005 at 2:57 am

  #552873

  thanks meghana and farrell... it helps alot...