C# SMO- checking if the table already exists at the destination server

Question

I have written a C# code to transfer tables between two remote servers using SMO, what I want to know is that is there anyway to check if the table already exists in the destination server with the exact schema, column names, data types, constraints and everything. Then I wouldn't have to drop the existing table every time and create new ones.

I have an easier approach in a similar question: http://stackoverflow.com/a/25273567/2188550 — , Aug 12 '14 at 20:36

Furqan Safdar · Accepted Answer · 2012-10-30T10:52:45.733

Try this piece of code snippet:

Server srv1 = new Server("<server_location>");
srv1.ConnectionContext.LoginSecure = false;
srv1.ConnectionContext.Login = "<username>";
srv1.ConnectionContext.Password = "<password>";
srv1.ConnectionContext.Connect();
Database sourceDb = srv1.Databases["<database_name>"];
Table sourceTbl = sourceDb.Tables["<table_name>"];

Server srv2 = new Server("<server_location>");
srv2.ConnectionContext.LoginSecure = false;
srv2.ConnectionContext.Login = "<username>";
srv2.ConnectionContext.Password = "<password>";
srv2.ConnectionContext.Connect();
Database destinationDb = srv1.Databases["<database name>"];
Table destinationTbl = sourceDb.Tables["<table_name>"];

var isMatched = CompareTables(sourceTbl, destinationTbl);

Compare Method:

bool CompareTables(Table source, Table destination)
{
    // Column count doesn't match
    if (!source.Columns.Count.Equals(destination.Columns.Count))
        return false;

    // Assuming the order of the Columns are same in both the Tables
    for (int i = 0; i < source.Columns.Count; i++)
        if (!source.Columns[i].Equals(destination.Columns[i]))
            return false;

    // Constraints count doesn't match
    if (!source.Checks.Count.Equals(destination.Checks.Count))
        return false;

    // Assuming the order of the Contraints are same in both the Tables
    for (int i = 0; i < source.Checks.Count; i++)
        if (!source.Checks[i].Equals(destination.Checks[i]))
            return false;

    return true;
}

won't this just check if the table of the same name and schema exists. I want columns,data types and constraints — Ejaz, Oct 30 '12 at 07:02
Check my answer now. I have now tested this piece of code at my side. Cheers! — Furqan Safdar, Oct 30 '12 at 09:49

C# SMO- checking if the table already exists at the destination server

1 Answers1