Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQl Server : Get size of tables in database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 132
    Comment on it

    Many times when we  manage SQL server databases we need to  determine how much space each table is consuming on disk. In this blog we will learn two approaches for solving this frequently encountered common problem.

    Approach 1:

    We can use the below query to get table wise usage details:

    SELECT 
        tab.NAME AS [Table Name],
        sch.Name AS [Schema Name],
        prt.rows AS [Row Counts],
        SUM(aunit.total_pages) * 8 AS [Total Space in KB], 
        SUM(aunit.used_pages) * 8 AS [Used Space in KB], 
        (SUM(aunit.total_pages) - SUM(aunit.used_pages)) * 8 AS [Unused Space in KB]
    FROM 
        sys.tables tab
    INNER JOIN      
        sys.indexes indx ON tab.OBJECT_ID = indx.object_id
    INNER JOIN 
        sys.partitions prt ON indx.object_id = prt.OBJECT_ID AND indx.index_id = prt.index_id
    INNER JOIN 
        sys.allocation_units aunit ON prt.partition_id = aunit.container_id
    LEFT OUTER JOIN 
        sys.schemas sch ON tab.schema_id = sch.schema_id
    WHERE 
        tab.is_ms_shipped = 0
    	AND 
    	indx.OBJECT_ID > 255 
    GROUP BY 
        tab.Name, sch.Name, prt.Rows
    ORDER BY 
        tab.Name
    


    Approach 2:

    If you are using SQL Server Management Studio, you can avoid  running the above  query and instead use a report.

    To access the report follow the below steps:

    1. Right click on the database
    2. Navigate to Reports > Standard Reports > Disk Usage By Table


     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: