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.