Skip to main content

Getting the size of your Microsoft (MS) SQL database

I foudn this at Stack Overflow, and thought it woud be great to save it since it works well.
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
 
It returns a table that you can use to calculate the total size. Original post Get size of all tables in database

Obvioulsy you must use the database first.

Comments

Popular posts from this blog

POST as form using C# to Pay Provider

The Remote Post Form class I have used the code for the jigar site and added my own goodies, so all credit to him. Sorry about the flast formating but is the only way I could get a neatish post using System; using System.Collections.Generic; using System.Web; using System.Security.Cryptography; /// <summary> /// Remotely handle and Post form from http://www.jigar.net/articles/viewhtmlcontent78.aspx%20 /// </summary> public class RemotePost { private System.Collections.Specialized.NameValueCollection Inputs = new System.Collections.Specialized.NameValueCollection(); public string Url; public string Method; public string FormName; public RemotePost(string pURL) { // // TODO: Add constructor logic here // Url = pURL; Method = "post"; FormName = "frmRemotePost"; }   public void Add(string name, string value) { Inputs.Add(name, value); } public void

Fixing winmail.dat problem - specifically in Quickbooks

For months we have had problems with attachments from Quickbooks. Having looked down many avenues I think we have found a fix or few: (See below for update) Here are a few websites that help out: Microsoft Outlook/Exchange MS-TNEF handling (aka "Winmail.dat", "Win.dat", or "Part 1.2" problem of unopenable email attachments) http://news.office-watch.com/t/n.aspx?a=716 KB958012 : When you use Outlook 2007 to send an e-mail message, the recipient of the message sees an attachment that is called Winmail.dat Essentially the summary is that there is a problem with Outlook trying to force Rich Text Format. so you need to turn that off. Mail users not receiving email in an Outlook derivative will have a problem. This format is called So: Go into Outlook On the "Tools" menu, click "Options", then click the "Mail Format" tab, and then the "Internet Format" button. Set "When sending Outlook Ri

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil