Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: Table and Database size

    Hi All,
    How can I list all the user define table with table size ?
    I know that I can get it through system procedure sp_spaceused <table name> however I want it for all the table object ?

    Also I want to know the total size, free size of database.

    Thanks

  2. #2
    Join Date
    Feb 2012
    Posts
    133
    you can try select statement..

    select 'sp_spaceused ' + name + char(10) + 'GO' from SYSOBJECTS where TYPE = 'U'

    for total size and free size database.
    I used this script found over internet also..


    this is for ver. 12.5.3. not sure if this can be use on ver > 15.0

    select "Database," = convert(char(20),db_name(dbid))+',',
    "Data Size," = str(sum(size * abs(sign(segmap - 4))) / 512.0, 7, 2)+',',
    "Data Used," = str(sum((size - curunreservedpgs(dbid, lstart, unreservedpgs)) * abs(sign(segmap - 4))) / 512.0, 7, 2)+',',
    "Data Free," = str(100.0 * sum((curunreservedpgs(dbid, lstart,unreservedpgs)) * abs(sign(segmap - 4))) / sum(size * abs(sign(segmap- 4))), 3) + "%"+',',
    "Log Size," = str(sum(size * (1 - abs(sign(segmap - 4)))) / 512.0, 7, 2)+',',
    "Log Used," = str(sum((size - curunreservedpgs(dbid, lstart, unreservedpgs))* (1 - abs(sign(segmap - 4)))) / 512.0, 7, 2)+',',
    "Log Free" = str(100.0 * sum((curunreservedpgs(dbid, lstart,unreservedpgs))* (1 - abs(sign(segmap - 4)))) / sum(size * (1 - abs(sign(segmap - 4)))), 3) + "%"
    from master..sysusages
    where segmap < 5
    group by db_name(dbid)
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •