c# – SMO – tables in schema other than dbo are not found

I need to generate a Create Table script for specific table.
The code I have so far is like this, the method takes tbName (table name) as an argument.

string sqlServerLogin = sourceDb.userId;
string password = sourceDb.password;
string serverName = sourceDb.serverName;
string schema = "custom";

ServerConnection serverConnect = new ServerConnection(serverName);
serverConnect.LoginSecure = false;
serverConnect.Login = sqlServerLogin;
serverConnect.Password = password;
Server server = new Server(serverConnect);
Database database = new Database(server, "devData");
List<Urn> list = new List<Urn>();
DataTable dataTable = database.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow row in dataTable.Rows)
   list.Add(new Urn((string)row["Urn"]));

 Scripter scripter = new Scripter();
 scripter.Server = server;
 scripter.Options.IncludeHeaders = true;
 scripter.Options.DriAllKeys = true;
 scripter.Options.DriIndexes = true;
 scripter.Options.DriAllConstraints = true;
 scripter.Options.ClusteredIndexes = true;
 scripter.Options.NonClusteredIndexes = true;

 Table table = database.Tables[tbName, Schema];
 scripter.Script(new Urn[] { table.Urn });

  StringCollection sc = scripter.Script(new Urn[] { table.Urn });
  foreach(string script in sc)
      sb.AppendLine("--create table");
      sb.Append(script + ";");

   return sb.ToString();

But when I run this it throws an error saying table is null.
I thought the problem was with the connection at first but after changing it to a table in a dbo schema, it was able to generate the script just fine.

I’m new to using SMO so any help is appreciated

Product of the Month September 2016

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *