locked
How can I analyze the size accupied be each table of my database RRS feed

  • Question

  • Hi guyz,

    I wonder if I could analyze the size accupied be each table of my database, I wanna know who is consuming the size.

    Tuesday, September 28, 2010 2:41 PM

Answers

  • SET NOCOUNT ON
    
      DECLARE @Database VARCHAR(128)
      DECLARE @SqlStr   VARCHAR(128)
      DECLARE @tblName  VARCHAR(128)
    
     SET @Database = DB_NAME(DB_ID())
    
     CREATE TABLE #ListOfTables
      (
       TbName VARCHAR(150)
       )
     
     SELECT @SqlStr = 'INSERT #ListOfTables SELECT TABLE_NAME FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
     EXEC (@SqlStr)
     
     CREATE TABLE #SpaceUsed
      (
       TableName   VARCHAR(150),
       QtyRows    VARCHAR(15),
       SpaceReserved VARCHAR(20),
       DataSize   VARCHAR(20),
       IndexSize   VARCHAR(20),
       UnusedSpace  VARCHAR(20)
       )
     
    
     SELECT @tblName = ''
     WHILE EXISTS (SELECT * FROM #ListOfTables WHERE TbName > @tblName)
     BEGIN
     SELECT @tblName = MIN(TbName) FROM #ListOfTables WHERE TbName > @tblName
     SELECT @SqlStr = 'EXEC [' + @Database + ']..sp_EXECutesql N''INSERT #SpaceUsed EXEC sp_spaceused [' + @tblName + ']'''
     EXEC (@SqlStr)
     END
    
      SELECT TableName, QtyRows,
          CAST(LEFT([DataSize],LEN([DataSize])-3) AS INT)/1000 as MB
      FROM #SpaceUsed
      ORDER BY MB DESC
     
      DROP TABLE #ListOfTables
     DROP TABLE #SpaceUsed
    
    

    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by Dr.X1987 Wednesday, September 29, 2010 4:22 PM
    Tuesday, September 28, 2010 2:45 PM
  • Yes, do not divide by 1000.

    CAST(LEFT([DataSize],LEN([DataSize])-3) AS INT) as KB


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by Dr.X1987 Wednesday, September 29, 2010 4:22 PM
    Tuesday, September 28, 2010 4:40 PM

All replies

  • SET NOCOUNT ON
    
      DECLARE @Database VARCHAR(128)
      DECLARE @SqlStr   VARCHAR(128)
      DECLARE @tblName  VARCHAR(128)
    
     SET @Database = DB_NAME(DB_ID())
    
     CREATE TABLE #ListOfTables
      (
       TbName VARCHAR(150)
       )
     
     SELECT @SqlStr = 'INSERT #ListOfTables SELECT TABLE_NAME FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
     EXEC (@SqlStr)
     
     CREATE TABLE #SpaceUsed
      (
       TableName   VARCHAR(150),
       QtyRows    VARCHAR(15),
       SpaceReserved VARCHAR(20),
       DataSize   VARCHAR(20),
       IndexSize   VARCHAR(20),
       UnusedSpace  VARCHAR(20)
       )
     
    
     SELECT @tblName = ''
     WHILE EXISTS (SELECT * FROM #ListOfTables WHERE TbName > @tblName)
     BEGIN
     SELECT @tblName = MIN(TbName) FROM #ListOfTables WHERE TbName > @tblName
     SELECT @SqlStr = 'EXEC [' + @Database + ']..sp_EXECutesql N''INSERT #SpaceUsed EXEC sp_spaceused [' + @tblName + ']'''
     EXEC (@SqlStr)
     END
    
      SELECT TableName, QtyRows,
          CAST(LEFT([DataSize],LEN([DataSize])-3) AS INT)/1000 as MB
      FROM #SpaceUsed
      ORDER BY MB DESC
     
      DROP TABLE #ListOfTables
     DROP TABLE #SpaceUsed
    
    

    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by Dr.X1987 Wednesday, September 29, 2010 4:22 PM
    Tuesday, September 28, 2010 2:45 PM
  • ok thanks,but I want to present the size in kelobyte can I??
    Tuesday, September 28, 2010 4:29 PM
  • Yes, do not divide by 1000.

    CAST(LEFT([DataSize],LEN([DataSize])-3) AS INT) as KB


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by Dr.X1987 Wednesday, September 29, 2010 4:22 PM
    Tuesday, September 28, 2010 4:40 PM
  • Take a look at this script 
    IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
      DROP TABLE #TablesSizes
      
    CREATE TABLE #TablesSizes (TableName sysname, ROWS BIGINT, reserved VARCHAR(100), DATA VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))
     
    DECLARE @SQL VARCHAR(MAX)
    SELECT @SQL = COALESCE(@SQL,'') + '
    insert into #TablesSizes execute sp_spaceused ' + QUOTENAME(Table_Name,'''') FROM INFORMATION_SCHEMA.TABLES
     
    --print (@SQL)
    EXECUTE (@SQL)
     
    SELECT * FROM #TablesSizes ORDER BY TableName
    

    from this blog post

    How to get information about all databases without a loop


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, September 28, 2010 5:07 PM
    Answerer