"I've been maintaining a 'certain' application for several months now," Trent writes, "it exists in a wonderful state of being partially properly written code, but mostly legacy garbage. I've done my best to avoid anything in the database realm, but a change request forced me to journey down that dark path."

"When scrolling through the countless number of tables, I noticed something called tblIsThere. This is what it looked like:"

"Curious, I did a quick search in the code base to find this:"

Public Shared Function TableExists(ByVal TableName As String) As Boolean
    Dim SQL As String = _
        "IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
	+ " WHERE TABLE_NAME = 'tblIsThere')"
	+ " CREATE TABLE [dbo].[tblIsThere] ([isthere] [bit] NOT NULL)"
    Dim theCMD As New SqlClient.SqlCommand(SQL, clsConnect.connect)
    Dim itDA As New SqlClient.SqlDataAdapter(_
        "SELECT * FROM tblIsThere", clsConnect.connect)
    Dim itDT As New DataTable
    Dim itDV As New DataView(itDT)

    If theCMD.Connection.State <> ConnectionState.Open Then
        theCMD.Connection.Open()
    End If

    Try
        theCMD.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try

    itDA.Fill(itDT)
    If itDV.Count = 0 Then
        SQL = "INSERT INTO tblIsThere (isthere) VALUES (0)"
    Else
        SQL = "UPDATE tblIsThere SET isthere = 0"
    End If
    theCMD.CommandText = SQL
    Try
        theCMD.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try

    SQL = "IF EXISTS(SELECT TABLE_NAME "
        + "FROM INFORMATION_SCHEMA.TABLES "
	+ "WHERE TABLE_NAME = '" & TableName & "') "
	+ "UPDATE tblIsThere SET isthere = 1"
    theCMD.CommandText = SQL
    Try
        theCMD.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try

    itDT.Clear()
    itDA.Fill(itDT)

    Return itDV.Item(0)("isthere")
End Function

"Luckily, the method is only used in clsUpgrade, which has since been replaced with a much nicer system that doesn't involve 20,000+ lines of SQL and dodgy logic hard coded into a single file."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!