Find size of all tables in a database

  • Comments posted to this topic are about the item Find size of all tables in a database

  • I think while this is a good script...the build it report for max storage used by tables is faster if you needed a quick look..

    of course the query can be twiked...to see size in mb or gb etc..but then I guess the query used by build in report can be captured by profier and twiked too..

    any ideas!!:-P

  • good script, but it's not suit SQLServer2005;

  • it works fine for me using it on 2005...post your errors..

  • The same can be done using sp_msforeachtable

    See below script

    ---------------------------------------------------------------------

    Create table #tmptblsize

    (table_name varchar(50),

    rows int,

    reserved varchar(50),

    actualdatasize varchar(50),

    indexsize varchar(50),

    unused varchar(50))

    EXEC sp_MSforeachtable 'insert into #tmptblsize exec sp_spaceused ''?'''

    select * from #tmptblsize

    drop table #tmptblsize

    --------------------------------------------------------------------

    Chandu

  • That is the same error I see on our 2005 server.

    Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62

    The object 'ComplaintCodes' does not exist in database 'CFS' or is invalid for this operation.

  • Hi,

    I used your script and it failed on AdventureWorks2008R2.

    The script supplied by chandu.ade works fine. I'm using it on more environments.

    Thanks

    Igor Micev,My blog: www.igormicev.com

  • To get the scrip to work with AdventureWorks2008R2 I changed the insert into @v_Table to:

    Insert Into @v_Table

    Select Table_Schema + '.' + Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'

    Unfortunatley sp_spaceused doesn't return the schema!

  • Hi All,

    I'm gettting this error from Suba' script.

    Errors:

    Msg 208, Level 16, State 1, Line 11

    Invalid object name 'Information_Schema.Tables'.

    Please advise.

    Thanks.

  • Hi Peter,

    Information_Schema.Tables is a system view. It is available as part of all sql server databases. Kindly let me as to which version of sql server you are using.

  • tweaked your code little to include size in MB and also for all schemas.

    Declare @v_TableName Varchar(100)

    Declare @v_Table Table (Table_Name Varchar(100))

    Declare @v_TableSize Table (

    Table_Name Varchar(100),

    rows BigInt,

    Reserved Varchar(50),

    ActualDataSize Varchar(50),

    IndexSize Varchar(50),

    Unused Varchar(50)

    )

    Insert Into @v_Table

    Select Table_Schema + '.' + Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'

    Select @v_TableName = Table_Name From @v_Table

    While Exists (Select 1 From @v_Table)

    Begin

    Insert Into @v_TableSize exec sp_spaceused @v_TableName

    Delete From @v_Table Where Table_Name = @v_TableName

    Select @v_TableName= Table_Name From @v_Table

    End

    --Select replace(ActualDataSize, ' KB', ''), * From @v_TableSize Order By rows Desc

    Select *, Cast(round(convert(decimal(10,2), replace(ActualDataSize, ' KB', ''))/1024.00,2) as decimal(10,2)) as 'Size in MB' From @v_TableSize Order By convert(bigint, replace(ActualDataSize, ' KB', '')) Desc

    Go

  • suba.sathyanathan 40131 (10/18/2012)


    Hi Peter,

    Information_Schema.Tables is a system view. It is available as part of all sql server databases. Kindly let me as to which version of sql server you are using.

    I get the same error, if you're defaulted into a database with a case sensitive collation, you'll need all caps, ie., SELECT * FROM INFORMATION_SCHEMA.TABLES.

  • Cheers for the script, I made a few adjustments to include the schema in the table Name (we have some databases which have multiple schemas). Also made a change to display the information as integers in GB and ordered by Total data consumed of indexes and table size.

    Incase anyone has any use for my changes here's my version.

    DECLARE @v_TableName VARCHAR(1500)

    DECLARE @v_Table TABLE

    (

    Table_Name VARCHAR(1500)

    )

    DECLARE @v_TableSize TABLE

    (

    Table_Name VARCHAR(1000) ,

    rows BIGINT ,

    Reserved VARCHAR(500) ,

    ActualDataSize VARCHAR(500) ,

    IndexSize VARCHAR(500) ,

    Unused VARCHAR(500)

    )

    INSERT INTO @v_Table

    SELECT TABLE_SCHEMA + '.' + Table_Name

    FROM Information_Schema.Tables

    WHERE Table_Type = 'BASE TABLE'

    SELECT @v_TableName = Table_Name

    FROM @v_Table

    WHILE EXISTS ( SELECT 1

    FROM @v_Table )

    BEGIN

    INSERT INTO @v_TableSize

    EXEC sp_spaceused @v_TableName

    DELETE FROM @v_Table

    WHERE Table_Name = @v_TableName

    SELECT @v_TableName = Table_Name

    FROM @v_Table

    END

    SELECT Table_Name ,

    rows ,

    (CAST(REPLACE(reserved, 'KB', '') AS INT)/1000) AS 'Total Used GB' ,

    (CAST (REPLACE(ActualDataSize, 'KB', '') AS INT)/1000) AS 'Table Data GB' ,

    (CAST (REPLACE(IndexSize, 'KB', '') AS INT)/1000) AS 'Index Data GB' ,

    (CAST(REPLACE(unused, 'KB', '') AS INT)/1000) AS 'Unused Allocation'

    FROM @v_TableSize

    ORDER BY 'Total Used GB' DESC

    Go

  • DECLARE @v_TableSize TABLE (

    Table_Name VARCHAR(100)

    ,ROWS BIGINT

    ,Reserved VARCHAR(50)

    ,ActualDataSize VARCHAR(50)

    ,IndexSize VARCHAR(50)

    ,Unused VARCHAR(50)

    )

    INSERT INTO @v_TableSize

    EXEC sys.sp_MSforeachtable 'sp_spaceused ''?'''

    SELECT *

    FROM @v_TableSize

    i think this is enough

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • Regardless of which method used, you should really look up the data types & sizes in BOL so you can properly declare the columns / variables ...

    for INFORMATION_SCHEMA.TABLES:

    TABLE_SCHEMA nvarchar(128)

    TABLE_NAME sysname

    for sp_spaceused:

    name nvarchar(128)

    rows char(11)

    reserved varchar(18)

    data varchar(18)

    index_size varchar(18)

    unused varchar(18)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply