Find size of all tables in a database

  • suba.sathyanathan 40131

    SSC Enthusiast

    Points: 191

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

  • hp_dba_uk

    Old Hand

    Points: 391

    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

  • caoxp930

    Grasshopper

    Points: 24

    good script, but it's not suit SQLServer2005;

  • hp_dba_uk

    Old Hand

    Points: 391

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

  • chandu.ade

    Mr or Mrs. 500

    Points: 508

    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

  • mpilchard@gmail.com

    SSC-Addicted

    Points: 436

    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.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • petes 78713

    Grasshopper

    Points: 21

    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!

  • Peter2012

    SSCommitted

    Points: 1652

    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.

  • suba.sathyanathan 40131

    SSC Enthusiast

    Points: 191

    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.

  • kannan ramasamy

    SSC Veteran

    Points: 203

    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

  • patrickmcginnis59

    SSCertifiable

    Points: 6483

    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.

  • james.anderson 82942

    SSC Enthusiast

    Points: 133

    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

  • thava

    SSCrazy

    Points: 2275

    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]

  • WILLIAM MITCHELL

    SSChampion

    Points: 13692

    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 15 (of 15 total)

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