HOW TO find the biggest tables in a database

acperkins has an answer:

Create Table #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))

SET NOCOUNT ON
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''

select a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by
a.table_name, a.row_count, a.data_size
Order by CAST(Replace(a.data_size, ' KB', '') as integer) desc
drop table #temp

sp_MSforeachtable is an undocumented stored procedure that can be used to perform the same actions for all tables in the database.

Comments

Post a Comment

Popular posts from this blog

Datawrapper Makes Data Beautiful & Insightful

Datawrapper is an online data visualization tool available at datawrapper.de that lets you create charts, maps and tables. Here's the list of the different visualizations you can create - Bar Chart Stacked Bars Grouped Bars Split Bars Bullet Bars Column Chart Stacked Columns Grouped Columns Lines Multiple Lines Area Chart Scatter Plot Dot Plot Range Plot Arrow Plot Election Donut Pie Chart Multiple Pies Donut Chart Multiple Donuts Choropleth map Symbol map Locator map Table It is self-funded by its customers’ subscription revenue. It also provides a good Free Plan .  The Datawrapper “Created with Datawrapper” attribution appears at the bottom of the chart if you use the free plan. However, you retain full copyright to your charts, maps, and tables. To remove the attribution, you’ll need to update to Custom or Enterprise plan. The Datawrapper River is a place to exchange charts and maps with other Datawrapper users. After adding a chart to the River, anyone can use it and customiz...
Read more

GitHub Copilot Q&A - 1

Image
Key takeaways from the talk in Q&A format - What is GitHub Copilot and how does it work? GitHub Copilot is a suite of tools designed for code completion. It works by analysing the code and comments you type in your IDE and sending that context to GitHub. Copilot then uses OpenAI's language model (in December 2024, it is GPT 4o by default and you can also choose the preview versions of o1-mini & o1-preview if you like) to understand your intent and generate suggestions for code completion. You can then accept, reject, or modify these suggestions. What context does GitHub Copilot consider when making suggestions? Copilot considers the entirety of the file you are currently working on, not just the code from your cursor position upwards. It also analyses the content of other open tabs in your IDE, starting with those closest to the current tab. This helps Copilot focus on the most relevant information. Is it necessary to meticulously manage open tabs for Copilot to work effect...
Read more

This Week I Learned - Week #3 2025

Image
This Week I Learned -  * AI coding assistants Cursor, Windsurf, Github Copilot offer features like code generation, context awareness, tab completion, multi-file support, Chat, code review, customization and choice of models. Cursor & Windsurf are AI-powered IDEs built on top of Visual Studio while Github Copilot integrates with various IDEs — VS Code, IntelliJ, Neovim. Code.  *  The Hundred-Page Machine Learning Book  by Andriy Burkov is provided on a read first, buy later basis * "I clearly see scope for Indian services to grow as India’s tech sector is thriving, shifting from traditional labour arbitrage to value-added services...Services trade will increase as US companies set up captive global capability centers (GCCs) here. GCCs will actually be a boon because if you get more jobs in India, less people will go there." - Syed Akbaruddin, former diplomat * Certain pathogens, like viruses and bacteria, can cause long-term inflammation or directly damage ...
Read more