How to check if table exists in a migration?

Question

This is as close as I've got...

public static class Helpers
{
    public static bool TableExists(this MigrationBuilder builder, string tableName)
    {
        bool exists = builder.Sql($@"SELECT 1 FROM sys.tables AS T
                     INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                     WHERE S.Name = 'SchemaName' AND T.Name = '{tableName}'");

        return exists;
    }

}

But how does one get a result form the SQL call?

Haslam

I've been trying to figure this out too. But it looks like it's not possible without an immense amount of work. – Boot

@leen3o I did the table creation with SQL. – Haslam

I'm wondering the same thing. Anyone have the solution? – Calendra

Answer 1

Here is one solution...

public override void Up()
{
    if (!Exists("dbo.MyTable"))
    {
        ... do something
    }
}

private static bool Exists(string tableName)
{
    using (var context = new DbContext(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        var count = context.Database.SqlQuery<int>("SELECT COUNT(OBJECT_ID(@p0, 'U'))", tableName);

        return count.Any() && count.First() > 0;
    }
}

This query runs immediately rather than being defered like other DbMigration commands are - but that's why it works. The result is known straight away so that other commands can be queued (or not queued) as required.

Egwin

Answer 2

It's not a perfect solution, but you could use an IF in SQL:

builder.Sql(@"
    IF (EXISTS(SELECT * 
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'MySchema'
        AND  TABLE_NAME = 'TableName'))
    BEGIN
        --DO SOMETHING
    END
");

Semipermeable

SQL Server 2016+ you can use DROP TABLE IF EXISTS MySchema.TableName – Numismatics

Answer 3

Here is something of brute force approach:

public static class MigrationBuilderExtensions
{
    // only included because I didn't want to back it out
    private static string Bracketed(string? name)
    {
        return 
            name.IsNullOrEmpty() 
            ? string.Empty 
            : $"[{ name?.Replace("[", string.Empty).Replace("]", string.Empty).Trim() }]";
    }

    public static OperationBuilder<SqlOperation> DropTableIfExists(this MigrationBuilder migrationBuilder, string name, string? schema = default)
    {
        var prefix = schema.IsNullOrEmpty() ? string.Empty : $"{Bracketed(schema)}.";
        return migrationBuilder.Sql($"DROP TABLE IF EXISTS {prefix}{Bracketed(name)};");
    }
/* ... other drop methods are similar */
}

and then in your migration script

public override void Up()
{
    migrationBuilder.DropTableIfExists("MyTable", "dbo");

    /* code to create the table of your dreams goes here */
}

Butte