Column-oriented databases, just another post

The debate about column-oriented databases and relational databases continues. Conor Cunningham published this post recently, which is a good read.

Fight!

Getting table size per database

The script below will give a simple list of much space each object within your database uses.

I can’t remember where I got it. If you recognize it, please let know.

-- Create the temp table for further querying CREATE TABLE #temp(     rec_id        int IDENTITY (1, 1),     table_name    varchar(128),     nbr_of_rows    int,     data_space    decimal(15,2),     index_space    decimal(15,2),     total_size    decimal(15,2),     percent_of_db    decimal(15,12),     db_size        decimal(15,2)) -- Get all tables, names, and sizes EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",             @command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)" -- Set the total_size and total database size fields UPDATE #temp SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp) -- Set the percent of the total database size UPDATE #temp SET percent_of_db = (total_size/db_size) * 100 -- Get the data SELECT * FROM #temp ORDER BY total_size DESC DROP TABLE #temp