Find table size in SQL Server

Hi There,

Worried about how to find which table contains more records, Which table consuming more space in your database.

Just simply run the Query given below. Your problem solved.

SELECT 

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.NAME NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255 

GROUP BY 

    t.Name, s.Name, p.Rows

ORDER BY 

    t.Name

Hope it will help you!. If yes please give your comment.

Thanks
Solomon S. 

Remove .aspx extension from ASP.Net application page on Runtime

Hey There,

It's being a long time I havn't write any thing to this blog. But today I have found very nice trick to avoid .aspx extension from the URL.


First you have to install a Package (Microsoft.AspNet.FriendlyURL) from Nuget

Install-Package Microsoft.AspNet.FriendlyUrls -Version 1.0.2

Then Simple add a Global.asax page in application and write a simple code in Application_Start section.

  protected void Application_Start(object sender, EventArgs e)
        {
            RouteConfig.RegisterRoutes(System.Web.Routing.RouteTable.Routes);
        }


Hope it will be helpful for you. Don'r forgot to comment if you face any problem or it resolved your problem.

Thanks
Solomon Sarkar