TSQL Challenges

TSQL Challenges intend to help you to test and enhance SET based querying skills using TSQL.





TSQL Challenge 7

The Context

You have done some Sql Server development tasks for a small company that has one Sql Server with several bases. Before you leave, as they will not have any dba to monitor databases, they ask you to write a simple script to help them monitor space used by their data.

The Challenge

You have to write the shortest script to list the 5 biggest tables on the server. It should include all databases that are not system databases. The resulting table will include only the following fields: BASE as database name, TABLE as table name, SIZE as table size used only by data (no index or unused reserved space) in KB and then ROWS as the number of rows in the table (in order to correlate if the space used is big by data or because of the number of rows).

This is an example resulting table:

BASE     TABLE   SIZE        ROWS      
----------------------------------
base1    T1      162720 KB   14233       
base2    T2      38000 KB    8462       
base1    T3      10720 KB    5343        
base1    T4      9040 KB     5006       
base3    T5      2800 KB     1899

The script should work on any SQL Server “as is”.

Notes:

  • The target of the challenge is to write the shortest code. We will only count the chars for this challenge, so don’t try to make it readable. Keep just the essential code.
  • You should write it as you want with any of the capabilities provided by a standard installation of SQL Server.
  • The script should target any version of SQL Server and it is not limited to only one query.
  • The script should not create any persistent objects (you should use temp tables or create proc/udf if you remove them after but not leave them on the server after your script runs).
  • The result has to be a table as described before (this means not a print) and should be reusable for future actions (we can plan for example to send the result table by mail or stored it on an other data table every 5 min with a job…)
  • Send your entries to tc@beyondrelational.com
  • Do not paste the code in the email body. Include it as an attachment: if possible a file named firstname_lastname_tsqlchallenge7.sql
  • Mention ‘TSQL Challenge 7’ in the email subject.
  • Last Date: 25 May 2009.
  • There is a forum linked to 'TSQL Challenges' for general questions and discussions on the 'TSQL Challenges' series.
  • For any questions or comments specific to TSQL Challenge 7, visit the dedicated thread
Posted: 05-16-2009 3:58 PM by Rui Carvalho | with 1 comment(s)
Filed under:

Comments

Antoine Gémis said:

Just for fun and for learning Powershell.

Here is my attempt with it. I think it's not very good as it was my first powershell script, but it works :-)

# 355 characters

$SqlServerIncludeSystemObjects=$False

dir `

|where{'ReportServer','ReportServerTempDB' -notcontains $_.Name} `

|foreach{$_.Tables|select Parent,Name,RowCount,DataSpaceUsed} `

|sort -p DataSpaceUsed -desc `

|select -F 5 `

|ft @{N='BASE';E={$_.Parent}},@{N='TABLE';E={$_.name}},@{N='SIZE';E={'{0} KB' -f $_.DataSpaceUsed}},@{N='ROWS';E={$_.RowCount}}

# Long one

# $SqlServerIncludeSystemObjects = $False

# dir `

# | where { 'ReportServer', 'ReportServerTempDB' -notcontains $_.Name} `

# | foreach {$_.Tables | select Parent, Name, RowCount, DataSpaceUsed} `

# | sort -p DataSpaceUsed -descending `

# | select -First 5 `

# | ft @{Name='BASE';Expression={$_.Parent}},@{Name='TABLE';Expression={$_.name}}, @{Name='SIZE';Expression={'{0} KB' -f

# $_.DataSpaceUsed}},@{Name='ROWS';Expression={$_.RowCount}}

*/

# July 1, 2009 7:35 AM