TSQL Challenge 7: Listing the 5 biggest tables on the server

A few weeks ago, I stumbled upon this blog that presents cool T-SQL challenges. I submitted an entry for challenge 7 that asked to write the shortest script to list the 5 biggest tables on a server. Here’s my solution:

create table #temp
(
	[database] nvarchar(MAX),
	[table] nvarchar(MAX),
	[rows] int,
	[reserved_size] nvarchar(100),
	[data_size] nvarchar(100),
	[index_size] nvarchar(100),
	[unused_space] nvarchar(100)
)

declare @sql nvarchar(MAX)
set @sql=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!)
  exec [~].dbo.sp_msforeachtable
    "insert into #temp([table], [rows], [reserved_size], [data_size], [index_size], [unused_space])
      exec [~].dbo.sp_spaceused !?!"','!',char(39))

EXEC sp_MSForEachDB
	@command1=@sql,
	@command2="update #temp set [database]='~' where [database] is null",
	@replacechar='~'

select top(5) [database] as base, [table], [data_size] as size, [rows] as rows
from #temp
order by Cast(LEFT([data_size],len([data_size])-3) as int) desc

drop table #temp

So, I started by creating a temporary table with columns (database, table, rows, reserved_size, data_size, index_size, unused_space) for the output. I used the two undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable to iterate through all the tables in all the databases and executed sp_spaceused as described in the following pseudo code:

foreach(database db in serverDatabases)
  if (db not in 'master', 'msdb', 'model', 'tempdb')
    foreach(table t in db.Tables)
    {
       insert into #temp (table, rows, reserved_size, data_size, index_size, unused_space)
         execute sp_spaceused for table 't'

       --at this point, our #temp table will be populated with data for each table
       --but the 'database' column will be 'null', so now replace it with the name of database
       update #temp
         set [database] = 'db' where [database] is null
    }

The most important part is that I am using an update operation for storing the database name in the temporary table. Thanks to Microsoft that we can give a set of 3 commands to the above mentioned undocumented stored procedures. Another hard part was to create a single t-sql statement that iterates for all tables inside a database and execute sp_spaceused. I did this by a complex combination of single quotes, double quotes and the replace function. In the last, I am just selecting the top(5) rows ordered by size.

To enter the contest, I reduced the script length by replacing all the variable/column names with a single length identifier. Here was my final submission:

create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))
declare @s nvarchar(MAX)
set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))
EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'
select top(5) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc
drop table #t

Let’s wait and see the solution of other players.

Advertisements

One Response to “TSQL Challenge 7: Listing the 5 biggest tables on the server”

  1. Victory in TSQL Challenge 7 « Mehroz’s Experiments Says:

    […] my solution to TSQL Challenge 7 was chosen among the winners. I briefly described my solution in this post. For result, have a look at […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: