Check if SQL table exists using INFORMATION_SCHEMA

Question

I have the following code to check if a SQL table exists:

using (var conn = new SqlConnection(SqlServerConnectionString))
{
    conn.Open();
    var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";
    var cmd = new SqlCommand(selectQuery, conn);
    var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        var count = reader.GetInt32(0);
        return count > 0;
    }
    reader.Close();
    conn.Close();
}

And it works fine. Is there a way to update this line to something more easily readable or easy to understand?

var count = reader.GetInt32(0);

Answer 1

This should work, and also fixes the nasty sql injection issue:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        int result = (int?)cmd.ExecuteScalar() ?? 0;
        return result > 0;
    }
}

But you still have this line that is somewhat cryptic:

int result = (int?)cmd.ExecuteScalar() ?? 0;

You could expand it into easier code like this:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        object result = cmd.ExecuteScalar();
        if (result == null) return false;

        return ((int)result) > 0;
    }
}

Newer versions of C# can shorten this again with Pattern Matching:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        return (cmd.ExecuteScalar() is int result && result > 0);
    }
}

Or more code, but maybe a little simpler to understand:

public bool TableExists(string tableName)
{
    var selectQuery = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(SqlServerConnectionString))
    using (var cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();

        if (cmd.ExecuteScalar() is int result)
        {
           return result > 0;
        }
    }
    return false;
}

But I'm guessing if you don't like the other code you won't like pattern matching, either.

Answer 2

There are several different ways to check if an table (or any other object) exists in the database.

This are very similar to search for every kind of object or just for tables:

SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]', N'U')

SELECT COUNT(*) FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[test]', N'U')

SELECT COUNT(*) FROM sys.objects WHERE name = N'test' AND schema_id = SCHEMA_ID(N'dbo') AND type = N'U'

or very short form this will return null if it does not exists or the object_id if it exists

SELECT OBJECT_ID(N'[dbo].[test]', N'U')

If you just want your c# code shorter/cleaner than you could do this:

using (var conn = new SqlConnection (SqlServerConnectionString))
{
  conn.Open ();
  var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName";
  var cmd = new SqlCommand (selectQuery, conn);
  cmd.Parameters.AddWithValue ("@tablename", tableName);
  var result = (int)cmd.ExecuteScalar ();

  return result > 0;
}

EDIT: as discussed in the comments I removed conn.Close()

using is just a short form for this to make sure that conn is disposed even if an exception occurs half way through the code

SqlConnection conn = null;
try
{
  conn = new SqlConnection (SqlServerConnectionString))

  conn.Open ();
  var selectQuery = $"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName";
  var cmd = new SqlCommand (selectQuery, conn);
  cmd.Parameters.AddWithValue ("@tablename", tableName);
  var result = (int)cmd.ExecuteScalar ();

  return result > 0;
}
finally
{
  conn?.Dispose ();
}