My Adventures in Coding

August 14, 2015

Java – Automate database schema updates with Flyway

Filed under: Java — Brian @ 1:44 pm
Tags: , , , , , ,

I am currently working on a Java 8 project which is a REST API deployed as a WAR file to Jetty. Our deploy process is very simple, our deploy pipeline just copies the WAR file into the Jetty directory in each environment then verifies the app is up and running with the correct version and runs some integration tests.

We wanted to be able to apply database migration scripts automatically in each environment (Dev, Test, QA, Staging, Prod) as we did our deploy, so we would no longer have to worry about manually applying scripts. In the past for Java, Scala, and .NET projects I have used several different tools, but for this project we decided to use Flyway which is very flexible and simple to setup.

The documentation for Flyway is excellent, however I decided to just post what we did in our app in case it might help someone else out. Here is our “Quick Start” setup.

1. Add the Flyway dependency

Flyway can be setup using Maven, Gradle, SBT, Ant, etc. In our project we used Maven, so all we did was add the following to our pom.xml file:


2. Code

Now, to get Flyway to check for any database changes to apply, whenever the application is started, put the following code somewhere in your applications startup sequence.

Flyway flyway = new Flyway();
//Create the dbo.schema_version table if it does not already exist
//Where the method "getDataSource()" provides your DataSource 
//object that has the jdbc url, username, and password.

3. SQL Script Location

All migrations scripts by default must go in the following folder in your app:


4. SQL Script Naming convention

Scripts are run in version number order based on their names, the default naming convention is:

For example: “V1_0_1__create_tables.sql”

All scripts must start with the letter “V”, followed by major/minor version numbers, with two underscores “__” separating the version from the description.

5. Schema Version Table

Flywaydb will automatically create a table in each database called “dbo.schema_version” which stores a log of all migration scripts that have been applied.

The table looks like:

version_rank installed_rank version description type script checksum installed_by installed_on execution_time success
1 1 1 Flyway Baseline BASELINE Flyway Baseline NULL JohnSmith 2015-07-30 15:55:49.337 0 1
1 2 1.0.1 create tables SQL V1_0_1__create_tables.sql -440358290 JohnSmith 2015-07-30 15:55:49.337 109 1

6. Handling Failure

If a script fails, the app will fail to start and the failure information is written to our log files as well as a monitor is tripped. Flywaydb applies each script as a transaction so all changes in the script will be rolled back if any part of the script fails. This is very handy because if you commit a script with some invalid SQL syntax, all you have to do is update the script with the corrected syntax, commit it again, and let the build pipeline apply the changes from the fixed script. No messy cleanup or reset to worry about.

So that is it, you should have all you need to get your database schema changes easily synced up with the deploy of your app!

June 7, 2015

Console2 – Mac(ish) terminal on Windows using Console2 and UnixUtils

Filed under: Windows — Brian @ 6:52 pm
Tags: , , , , ,

If you are like me and you are used to using a terminal window on a Mac, using the Windows command prompt can be a bit frustrating. I like having basic functionality like tabs, short cuts to switch tabs, cut and paste with Ctrl+c and Ctrl+v, dragging the corner of a terminal to resize, all of which I take for granted on my Mac. Fortunately, Console2 and UnixUtils can help (with a little customization).

Console2 is an open source project that provides a tabbed version of the windows console app. However, it is much more than that. Even though the default settings are less than desirable, Console2 is highly customizable. So you can easily change the defaults to have all kinds of amazing features such as “select to copy”, Ctrl+c to copy, Ctrl+v to paste, drag the corner of the window to resize it, short cuts to open new tabs, switch tabs, etc. Yep, you can make it function like a normal command prompt!

Download Console2

Download the zip file, extract it, and run Console2.exe.


So far it does not look like much of an improvement, but don’t worry, we have some customizing to do.

Go to Edit -> Settings

“Console” – set the Buffer size rows to something larger, such as 9999:

“Appearance” – customize the font, font size, and text colour. I like Consolas, 12 point font, and a nice green text colour to remind me of the default on my Mac.


“Appearance -> More” – hide the toolbar and status bar, ensure tabs are displayed, and set some cool transparency.


“Behaviour” – ensure “Copy on select” is checked. This will allow you to select text on the console window and have it automatically put on the clipboard (Woohoo!).


“Hotkeys” – set the “New Tab 1” hotkey to use Ctrl+T.


“Hotkeys” – set “Copy Selection” to Ctrl+c.

“Hotkeys” – set “Paste” to Ctrl+v.

“Hotkeys -> Mouse” – set “Copy/clear selection” to “None”.

“Hotkeys -> Mouse” – set “Select text” to “Left”.

“Hotkeys -> Mouse” – set “Paste” to “Right”.

“Hotkeys -> Mouse” – set “Context menu” to “None”.

“Tabs” – select “Console2” and set style to “XTerm”.

Now, here is where we get to the really cool part of Console2. Besides just having a windows console tab, you can also create tabs for any other console apps such as Cygwin, Gitbash, and PowerShell.

To show you how to setup a second type of command prompt, lets use PowerShell as an example.

“Tabs” – select “Add”

Enter a name for the new Console window (“PowerShell”), set the path to the PowerShell executable, and add an icon.
— %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe


Now you will see both “Console2” and “PowerShell” in the list of types of available tabs.

Of course, also install UnixUtils as well to make your life a little easier. To install UnixUtils, all you have to do is download the zip file, unzip it somewhere, such as “C:\UnxUtils” then add to your path “C:\UnxUtils\bin”. That is all, then you should have all of your favorite commands such as “ls” and “grep”.

Finally, a somewhat functional command prompt!



February 6, 2015

IIS – PowerShell script to add IIS URL Rewrite Rule

Filed under: IIS — Brian @ 11:50 am
Tags: , , , , , , , ,

We have a Java REST API that runs on the same server as a .NET application. We only allow port 80 to be open on our web server. For this reason we needed to create a URL Rewrite Rule in IIS to redirect external traffic to the REST API on port 80 to port 81. It is fairly easy to do this setup in IIS, however we always want to have all of our server setup scripted. So here is a simple PowerShell script to create a URL Rewrite rule:

In this example the application is called “fddapi” and the root of the application path is “/fddapi” which is the pattern we are looking for in the URL Rewrite rule.

$site = &quot;iis:\sites\Default Web Site&quot;
$filterRoot = &quot;system.webServer/rewrite/rules/rule[@name='fddapi$_']&quot;
Clear-WebConfiguration -pspath $site -filter $filterRoot
Add-WebConfigurationProperty -pspath $site  -filter &quot;system.webServer/rewrite/rules&quot; -name &quot;.&quot; -value @{name='fddapi' + $_ ;patternSyntax='Regular Expressions';stopProcessing='False'}
Set-WebConfigurationProperty -pspath $site  -filter &quot;$filterRoot/match&quot; -name &quot;url&quot; -value &quot;(fddapi)/.*&quot;
Set-WebConfigurationProperty -pspath $site  -filter &quot;$filterRoot/conditions&quot; -name &quot;logicalGrouping&quot; -value &quot;MatchAny&quot;
Set-WebConfigurationProperty -pspath $site  -filter &quot;$filterRoot/action&quot; -name &quot;type&quot; -value &quot;Rewrite&quot;
Set-WebConfigurationProperty -pspath $site  -filter &quot;$filterRoot/action&quot; -name &quot;url&quot; -value &quot;http://localhost:8081/{R:0}&quot;

After running the script your new URL Rewrite rule should now be created with the following settings:


So now any request to http://localhost/fddapi/someroute will be automatically redirected by IIS to our Java REST API (running on Jetty) to http://localhost:8081/fddapi/someroute and the caller will never know the difference!

January 11, 2015

Java to Sql Server – Cannot create PoolableConnectionFactory

Filed under: Uncategorized — Brian @ 11:14 pm

If you are connecting a Java application to a SQL Server database, which is up and running, but your connection fails with the following error:

Could not acquire a connection from DataSource – Cannot create PoolableConnectionFactory

The error is most likely that the windows service “SQL Server Browser” is disabled.

To fix the problem:

  • Start -> Control Panel -> Systems & Security -> Administrative tools -> Services
  • SQL Server Browser -> right click -> properties

image2014-10-30 19-17-34

  • Set Startup type to “Automatic”
  • Apply -> Start

image2014-10-30 19-19-16

Now you should be able to connect to SQL Server from your Java application!

IIS – Setup IIS as a proxy to Jetty

Filed under: .NET,IIS — Brian @ 12:35 am
Tags: , ,

On my current project we are working on two applications for a customer, one is in .NET and the other is in Java. The client only wants to have port 80 and 443 open on the server, however, we will need to have two web servers running, IIS for the .NET application and Jetty for the Java application. Our solution was to run the .NET application on IIS on port 80 and the Java application on Jetty on port 81, then have IIS route traffic for the Java application coming in on port 80 to port 81 using IIS as a proxy to Jetty. The following is a simple tutorial on how to setup IIS as a proxy to Jetty.

Install Application Request Routing (ARR)

Setup IIS to Jetty Redirect

For this example the route for the Jetty application will be “fddapi”.

Open IIS and on the Server select “Application Request Routing” icon.


Select “Server Proxy Settings”.


Set “Enable Proxy” setting and “Apply” the change.


Go to “Default Web Site”, select “URL Rewrite”.

4-Select Url ReWrite

Under the Actions menu select “Add Rules”.


Under “Inbound Rules” select “Blank Rule” and click “Ok”.


Edit the Inbound Rule with a pattern to match on and how to rewrite the url.


Test the Setup

Now open a browser and go to the url: http://localhost/fddapi/someroute which should work now without needing to specify port 8081.

July 30, 2014

Java – Creating a simple retry command with function passing in Java 8

Filed under: Java — Brian @ 8:45 am
Tags: , , ,

Recently we have been working on an application that imports data from a number of different sources, where the network connection between us and each of these sources is not very reliable. So in our Gateway that makes these REST calls I wanted to be able to write a reusable piece of code that we could use in different calls, that in the event of a failure, would retry the command a few more times before finally giving up.

Java 7
I wanted to be able to write this retry logic and error handling code in one place and use it for a number of different method calls. There are several ways to do this, but previously in Java 7 I would have just written an abstract class with a single abstract method such as:

public abstract class RetryCommand<T> {
    private int maxRetries;

    public RetryCommand(int maxRetries) {
        this.maxRetries = maxRetries;

    // This abstract command is the method that will be implemented 
    public abstract T command();

    public final T run() throws RuntimeException {
        try {
            return command();
        } catch (Exception e) {
            return retry();

    private final T retry() throws RuntimeException {
        System.out.println("FAILED - Command failed, will be retried " + maxRetries + " times.");
        int retryCounter = 0;
        while (retryCounter < maxRetries) {
            try {
                return command();
            } catch (Exception e) {
                System.out.println("FAILED - Command failed on retry " + retryCounter + " of " + maxRetries + " error: " + ex );
                if (retryCounter >= maxRetries) {
                    System.out.println("Max retries exceeded.");
        throw new RuntimeException("Command failed on all of " + maxRetries + " retries");

Then in my Gateway code, for each method that I want to wrap with my retry logic I would just do the following:

public class MyGateway {
    private RetryCommand<String> retryCommand;
    public MyGateway(int maxRetries) {
        retryCommand = new RetryCommand<>(maxRetries);

    // Inline create an instance of  the abstract class RetryCommand
    // Define the body of the "command" method
    // Execute the "run" method and return the result
    public String getThing(final String id) {
        return new RetryCommand<String>() {
            public String command() {
                return client.getThatThing(id);

The reason for this layout was I could not pass a function as a parameter to a method, like I have done in Scala, Python, and C#. However, now that we have Java 8, we can finally pass functions as parameters using the handy features in java.util.function package!

Java 8
Java 8 uses Functional Interfaces, which are interfaces with a single abstract method. The package java.util.function defines a number of standard functional interfaces, so most of the time you will be able to use one of these. Some example functional interfaces are Function (function with return value and input param), Supplier (function with return value but no input param), and Consumer (function with input param but no return value). However, if one of these standard functional interfaces does not meet your needs you can always define your own. In the following example I used Supplier.

So now in Java 8 I would create a new RetryCommand class that has a “run” method which takes in a function:

import java.util.function.Supplier;

public class RetryCommandJava8<T> {
    private int retryCounter;
    private int maxRetries;

    public RetryCommandJava8(int maxRetries)
        this.maxRetries = maxRetries;

    // Takes a function and executes it, if fails, passes the function to the retry command
    public T run(Supplier<T> function) {
        try {
            return function.get();
        } catch (Exception e) {
            return retry(function);

    public int getRetryCounter() { return retryCounter; }

    private T retry(Supplier<T> function) throws RuntimeException {
        System.out.println("FAILED - Command failed, will be retried " + maxRetries + " times.");
        retryCounter = 0;
        while (retryCounter < maxRetries) {
            try {
                return function.get();
            } catch (Exception ex) {
                System.out.println("FAILED - Command failed on retry " + retryCounter + " of " + maxRetries + " error: " + ex );
                if (retryCounter >= maxRetries) {
                    System.out.println("Max retries exceeded.");
        throw new RuntimeException("Command failed on all of " + maxRetries + " retries");

So now in my gateway code, I would create my fancy new retry command executer:

public class MyGateway {
    private RetryCommandJava8<String> retryCommandJava8;
    public MyGateway(int maxRetries) {
        retryCommandJava8 = new RetryCommandJava8<>(maxRetries);

    // Passing function using a Lamba expression 
    public String getThing(final String id) {
        return -> client.getThatThing(id));

Also, this setup is fairly easy to unit test, here are some example tests:

import junit.framework.TestCase;

public class RetryCommandJava8Test extends TestCase {

    public String SUCCESS = "success";
    public int MAXRETRIES = 3;
    public int SECONDSTOWAIT = 0;
    RetryCommandJava8<String> retryCommandJava8;

    public void testRetryCommandShouldNotRetryCommandWhenSuccessful() {
        retryCommandJava8 = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        String result = -> SUCCESS);

        assertEquals(SUCCESS, result);
        assertEquals(0, retryCommand.getRetryCounter());

    public void testRetryCommandShouldRetryOnceThenSucceedWhenFailsOnFirstCallButSucceedsOnFirstRetry() {
        retryCommand = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        String result = -> {
            if (retryCommand.getRetryCounter() == 0) throw new RuntimeException("Command Failed");
            else return SUCCESS;

        assertEquals(SUCCESS, result);
        assertEquals(1, retryCommand.getRetryCounter());

    public void testRetryCommandShouldThrowExceptionWhenMaxRetriesIsReached() {
        retryCommandJava8 = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        try {
   -> {throw new RuntimeException("Failed");});
            fail("Should throw exception when max retries is reached");
        } catch (Exception e) { }

Of course this example is a stripped down version of what we use, which does waits between retries, back off retries, and proper logging of errors, etc. I just wanted to use a retry command as my example code for trying out function passing in Java 8. However, I hope maybe you will find this useful if you are trying to get a working example going for your first use of function passing in Java 8.

If you are new to Java 8 (just like I am) I recommend reading Everything About Java 8.

May 2, 2014

SQL Server – Simple Recursive Query Example

Filed under: SQL,SQL Server — Brian @ 2:50 pm
Tags: , , , ,

Every once in a while I need to write a recursive query in SQL Server and I always forget the syntax so I have to track down a simple example to help me remember. However, if you are trying to write a recursive query for the first time, I find some of the examples online to be a little bit too complicated. So I wanted to post an example, but also give you the script to create the table and populate it with data so you can see how it works and try it yourself. In this post I use the common example of a table with countries, states, and cities and where we want to get a list of all cities in a single country. Enjoy!

Create a table called “Area”:

   AreaID int NOT NULL,
   AreaName varchar(100) NOT NULL,
   ParentAreaID int NULL,
   AreaType varchar(20) NOT NULL
( AreaID ASC

Add some “Area” data:

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(1, 'Canada', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(2, 'United States', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(3, 'Saskatchewan', 1, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(4, 'Saskatoon', 3, 'City')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(5, 'Florida', 2, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(6, 'Miami', 5, 'City')

If I do a select by AreaType “City”:

select * from dbo.Area
where AreaType = 'City'

I get both Saskatoon and Miami:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3            City
6       Miami           5            City

However, what if I wanted to return all cities in Canada?

You can accomplish this by doing a recursive select which uses a common table expression (CTE).

--anchor select, start with the country of Canada, which will be the root element for our search
SELECT AreaID, AreaName, ParentAreaID, AreaType
FROM dbo.Area 
WHERE AreaName = 'Canada'
--recursive select, recursive until you reach a leaf (an Area which is not a parent of any other area)
SELECT a.AreaID, a.AreaName, a.ParentAreaID, a.AreaType 
FROM dbo.Area a 
INNER JOIN AreasCTE s ON a.ParentAreaID = s.AreaID 
--Now, you will have all Areas in Canada, so now let's filter by the AreaType "City"
where AreaType = 'City' 

Now we get back the following results for cities in Canada:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3               City

That’s it! Now we have written a simple recursive query!

March 6, 2014

SQL Server – Alter database in Single User mode to Multi User mode

Filed under: SQL,SQL Server 2005 — Brian @ 8:43 am
Tags: , , , ,

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.

First, open a SQL Server Management Studio query window connected to database “master”.

The command to change the database back to multi user mode is:


However, if there is an existing process blocking this alter, you may get the error message:

“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”

Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.

To find the “spid” of the existing connection, you can use the following query:

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DatabaseNameGoesHere'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

exec sp_who

So, here is our revised command:

KILL SpidToKillGoesHere

That should do it!

February 24, 2014

C# – Mocking a method to return a different value when called a second time using Moq

Filed under: .NET,c#,Mocking,Testing — Brian @ 2:36 pm
Tags: , , , ,

I have been using Moq as my primary mocking framework in C# for several years now. Overall I really like it, the syntax is easy to read, the mock setup is straight forward, etc. However, there are a few cases I need to mock infrequently and I always forget how to do the setup. Often because I am cycling between Scala, Python, and C#. So in this post I am just listing those three cases in hopes they may help others, yet at the same time as a reference to myself!

So for this example, first here is my example service that we will be mocking in our tests:

namespace MoqExample
    public interface ISomeService
        SomeStuff GetNextStuff();
        void DoStuff();

    public class SomeService : ISomeService
        public SomeStuff GetNextStuff()
            return new SomeStuff();

        public void DoStuff()

    public class SomeStuff
        public int Id { get; set; }
        public string Name { get; set; }

Return a different value the second time a mocked method is called

Let’s say I have a call in a service that returns the next item to process, such as pulling in customer requests from a queue that need to be processed in order, one at a time. In this case I might want to have a test that demonstrates the application can handle two different requests back to back, or even handle the case where there is an item to process, but the next item returned is empty, meaning there is nothing left to process. If I need to test this case there are several simple ways to do this with Moq.

The more well known approach is to just use a Queue and have the mock call Dequeue and return the result each time the mocked method is called.

public void MogMethodThatReturnsADifferentValueWhenCalledASecondTimeUsingAQueue()
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    var queueStuff = new Queue<SomeStuff>();
    queueStuff.Enqueue(new SomeStuff { Id = 1, Name = "Real" });
    _mockSomeService.Setup(x => x.GetNextStuff()).Returns(queueStuff.Dequeue);


The alternative is to use a feature in Moq called Sequences which allows you to set multiple return values, that will be returned one at a time in order, each time the mocked method is called.

public void MogMethodThatReturnsADifferentValueWhenCalledASecondTimeUsingSequences()
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.SetupSequence(x => x.GetNextStuff())
		    .Returns(new SomeStuff {Id = 1, Name = "Real"})


Throw an exception the first time a mocked method is called, return a value the second time

In this case I want to test that my application will handle the case that when a call to a service throws an exception, it will retry and if it receives a valid response on the second try, process the request successfully and continue. You can accomplish this by using a feature in Moq called “Callback”.

public void MogMethodThatThrowsAnExceptionFirstTimeCalledAndAnObjectWithSecondTime()
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    var calls = 0;
    _mockSomeService.Setup(x => x.GetNextStuff())
	.Returns(() => new SomeStuff {Id = 1, Name = "Real"})
	.Callback(() =>
	    if (calls == 1)
		throw new Exception("Failure");

    Assert.Throws<Exception>(() => _mockSomeService.Object.GetNextStuff());

Now, it is true that you can use a Moq Sequence to return a different value each time a mocked method is called, but as far as I can tell you can only use this where the valid value is first and throwing an exception is the last item in the sequence. In my case above I explicitly wanted to test that an exception was thrown on the first call and a valid value was returned on the second call. However, if all you need to test in your code is how it handles a valid value on the first call and an exception being thrown on the second call, you can use a Sequence for your mock setup.

public void MockMethodThatReturnsAnObjectFirstTimeCalledAndThrowsAnExceptionSecondTimeUsingSequences()
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.SetupSequence(x => x.GetNextStuff())
	.Returns(new SomeStuff())

    Assert.Throws<Exception>(() => _mockSomeService.Object.GetNextStuff());  

Mock a void method to throw an exception

Lets say I have some void method that normally just silently does some task for me and has no need to have a return type, such as a call to write a stat or a log entry. However, if I want to test how my application handles the case when this call throws an exception, I can use the following setup to mock this method.

public void MogMethodThatThrowsAnExceptionIsVoidAssertExceptionIsThrown()
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.Setup(x => x.DoStuff()).Throws(new Exception("Failure"));

    Assert.Throws<Exception>(() => _mockSomeService.Object.DoStuff());

May 27, 2013

SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Filed under: SQL,SQL Server 2005 — Brian @ 11:46 am
Tags: , , , ,

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

That should do the trick!

Next Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 38 other followers