Table Exists

Determines whether a table with the specified name exists in the given SQL Server connection.

If the schema is not specified, the default schema “dbo” is assumed.
Square brackets around schema or table names are trimmed automatically.
Uses INFORMATION_SCHEMA to check for table existence with parameterized query to avoid SQL injection.
The MyConnection must be open before calling this method.

    Public Function TableExists(MyConnection As SqlConnection, 
                                tableName As String) As Boolean

        ' Split into schema and table
        Dim schema As String = "dbo"
        Dim name As String = tableName

        If tableName.Contains(".") Then
            Dim parts = tableName.Split("."c)
            If parts.Length = 2 Then
                schema = parts(0).Trim("["c, "]"c)
                name = parts(1).Trim("["c, "]"c)
            End If
        Else
            name = tableName.Trim("["c, "]"c)
        End If

        Dim cmdText As String = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES " +
                                " WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @name"

        Using cmd As New SqlCommand(cmdText, MyConnection)
            cmd.Parameters.AddWithValue("@schema", schema)
            cmd.Parameters.AddWithValue("@name", name)

            Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())
            Return count > 0
        End Using
    End Function

    Public Function TableExists(MyConnection As SqlConnection, 
                                tableName As String) As Boolean

        ' Split into schema and table
        Dim schema As String = "dbo"
        Dim name As String = tableName

        If tableName.Contains(".") Then
            Dim parts = tableName.Split("."c)
            If parts.Length = 2 Then
                schema = parts(0).Trim("["c, "]"c)
                name = parts(1).Trim("["c, "]"c)
            End If
        Else
            name = tableName.Trim("["c, "]"c)
        End If

        Dim cmdText As String = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES " +
                                " WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @name"

        Using cmd As New SqlCommand(cmdText, MyConnection)
            cmd.Parameters.AddWithValue("@schema", schema)
            cmd.Parameters.AddWithValue("@name", name)

            Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())
            Return count > 0
        End Using
    End Function

Posted by ipercube