Re-purpose my TableSize Script

Comments: No Comments
Published on: March 3, 2011

Last Year I introduced a couple of scripts that I worked on.  Those scripts developed into a series covering the comparison of some methods and MS provided stored procs that could help you in the retrieval of table sizes in SQL Server.

I pulled out one of those scripts recently in order to find what tables, in an R and D database, were consuming alot of space.  While running that script, I realized that it could easily be used to help me with an additional task.  I decided that this script could help me deduce the top 10 biggest tables in a database that doesn’t necessarily need to have that data.  Or maybe, I just need to clean out the data so I can test populating the database.  This script is predicated on a lack of foreign keys – but can easily be adapted.

So, in all of its glory:

[codesyntax lang=”tsql”]


I think the script, along with prior explanations, is pretty straight forward.  This can quickly help reset those LARGE tables for continued testing.  Of course, that is predicated on you not already having a script for that, and that you don’t know what tables need to be reset (maybe you are in a large team).

This is just one example of a script that can be useful for more than what it was first designed to do.  As DB professionals, we often come across situations were a prior script can easily be repurposed for the task at hand.  Knowing that, and how to do it, is an important tool in your toolbox.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

March 2011
« Feb   Apr »


  • @SQLTaiob: @MJSwart WHILE 1=1 BEGIN INSERT into sysadmin..__locks SELECT ....., getdate() record_date FROM sys.dm_tran_locks #sqlhelp
  • @Kendra_Little: @MJSwart Extended events! I wrote about this a while back, should update it. #sqlhelp
  • @SQLHA: @tkrussy But you're clearly having a network issue. You may want to look at the cluster log (Get-ClusterLog). #sqlhelp
  • @pmpjr: @tkrussy I left you a comment in the MS forum #sqlhelp
  • @SQLSoldier: @MJSwart sys.dm_tran_locks and serializable isolation level (or holdlock hint if a single query). #sqlhelp
  • @MJSwart: #sqlhelp The Lock event category is too chatty, and sp_whoisactive is point-in-time.
  • @MJSwart: #sqlhelp what's your favorite method of tracing important locks taken by a given sql server query?
  • @tkrussy: .@SQLHA none of WSFC logs show anything abnormal besides the event stating it brought SQL Server (MSSQLSERVER) online. #sqlhelp
  • @tkrussy: .@SQLHA completely understood, looking in EventVwr>Appli and service logs>FailoverClustering as well as Application and System Logs #sqlhelp
  • @SQLHA: @tkrussy No idea where you're looking. This is a WSFC or networking issue, not SQL Server. FCI != WSFC. #sqlhelp

Welcome , today is Tuesday, November 25, 2014