I came across this scenario many times when you need to find the largest tables in the database, specially at the time of maintenance or support. Fortunately we do have a solution in the shape of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single line of script.
Let me explain it step by step by step.
Step 1 :
First, open SQL Server Management Studio (SSMS) and select the target database (where you need to find the largest object) and right click on it as shown below.
Step 2 :
Once you right click on the target database you will see a lot of options. Select reports \ standard reports \ Disk Usage by Top Tables as shown below. There are other very useful reports that give you the statistical data about your SQL Server.
Step 3 :
Once you click on reports \ standard reports \ Disk Usage by Top Tables, SSMS will open a report showing which tables are using more Disk spaces, reserved space & having highest record counts etc as shown below.
Step 4 :
Now you have the report and by default it is sorted by Reserved (KB), but you can sort it with any column (# Records (Actually record count), Reserved(KB), Data(KB), Indexes(KB), Unused(KB)) by clicking on up and down sign as shown below.
The beauty of this feature (Standard reports) is that you can generate the statistical report within few seconds, just by click of a menu. In addition you can export / print this report in a nice format without any extra effort as shown below.
Note : Please do not forget to refresh this report before export or print ;).
Let me know if you tried this solution and how did you like it ?
SQL Server Portal 