TSQL Challenge 7 - Solution by Syed Mehroz Alam
Syed Mehroz Alam prensents us a nice solution using all the principles already described. As obvious, he’s solution is one of the sortests. But let’s present Syed berore running into it:
Syed Mehroz Alam, living in Karachi, Pakistan, is primarily a developer focusing Microsoft technologies. He has completed his bachelors as a Computer Systems Engineer in 2006 and is currently pursuing a Masters degree in Computer Science. Despite developing rich internet applications, he loves to work with SQL Business Intelligence platform that enhances his TSQL expertise. He is fond of logical challenges and has won several speed programming competitions which are listed here.
Mehroz writes articles at CodeProject, and expresses his experiences with .NET and SQL server at his blog. When he has time, he contributes to MSDN and Silverlight forums. He loves to play football (Soccer) and computer games, especially first person shooters and RPGs.
Here is it’s solution and explanation:
This is the original solution:
create table #temp
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))
@command2="update #temp set [database]='~' where [database] is null",
select top(5) [database] as base, [table], [data_size] as size, [rows] as rows
order by Cast(LEFT([data_size],len([data_size])-3) as int) desc
drop table #temp
So, I started by created 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_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
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
Thanks again Syed for sharing this solution with us and congratulations for being part of the winners!
As the TSQL Challenge #10 is available don’t forget to have a look on it.
I also invite you to see the post about “Invitation to volunteer with TSQL Challenges Committee”.
Happy Challenge now!