SQL SMO is generating insert statements incorrectly

Question

I'm working on a C# application to generate the script. I have the following settings below, but tables/insert statements are being generated for other schemas. I just want one specific schema and all the schema/data from it.

I tried to set options.WithDependencies = false;, but then it wouldn't give me everything I needed as in scripting the data.

What other setting am I missing?

        private void GeneratingMainSQLScript(string schema)
        {
            Server srv = new Server(@".\sql2017");
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.EnforceScriptingOptions = true;
            options.ScriptData = true;
            options.ScriptDrops = false;
            options.FileName = GenerateScriptFileName(schema, false);
            options.EnforceScriptingOptions = true;
            options.ScriptSchema = true;
            options.IncludeHeaders = true;
            options.AppendToFile = true;
            options.Indexes = true;
            options.WithDependencies = true;

            var tableNames = GetTableNames(schema);

            int progressCounter = 1;
            foreach (var tbl in tableNames)
            {
                if (tbl.SchemaName == "dbo" && tbl.TableName == "FileDatas")
                {
                    options.ScriptData = false;
                }

                dbs.Tables[tbl.TableName, tbl.SchemaName].EnumScript(options);

                DisplayDebug(string.Format("Running migration schema's ({2}) main genreated script | Total: {0} | Processing #: {1}", tableNames.Count, progressCounter, schema));

                progressCounter++;
            }
        }

UPDATE:

I made these changes after working with my lead and we are close. The issue now: it is generating the FKS after creating the table and not at the END of the file like when you do it via SSMS directly?

Server srv = new Server(@".\sql2017");
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.FileName = GenerateScriptFileName(schema, isSchemaOnly);
            options.EnforceScriptingOptions = true;
      
            // start
            options.AnsiPadding = false;
            options.AppendToFile = true;
            options.ContinueScriptingOnError = false;
            options.ConvertUserDefinedDataTypesToBaseType = false;
            options.WithDependencies = false;
            options.IncludeHeaders = true;
            options.IncludeScriptingParametersHeader = false;
            options.SchemaQualify = true;
            options.Bindings = false;
            options.NoCollation = true;
            options.Default = true;
            options.ScriptDrops = false;
            options.ScriptSchema = isSchemaOnly;
            options.ExtendedProperties = true;
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.TargetDatabaseEngineType = Microsoft.SqlServer.Management.Common.DatabaseEngineType.Standalone;
            options.LoginSid = false;
            options.Statistics = false;
            options.ScriptData = !isSchemaOnly;
            options.ChangeTracking = false;
            options.DriAllConstraints = true;
            options.ScriptDataCompression = false;
            options.DriForeignKeys = true;
            options.FullTextIndexes = false;
            options.Indexes = false;
            options.DriPrimaryKey = true;
            options.Triggers = false;
            options.DriUniqueKeys = true;
            options.DriAll = true;

Answer 1

Here is the solution I am using. The key here is all the options that I have set, but also how I pull the table names. GetTablesNames will obtain the tables in the order that they need to be made in, which allows for everything to process properly along with the FKs.

1. Obtaining the tables properly
2. setting this variable to false: options.WithDependencies = false;

Are the keys to my solution.

        private void GeneratingMainSQLScript(string schema, bool isSchemaOnly)
        {
            Server srv = new Server(sqlServer);
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.FileName = GenerateScriptFileName(schema, isSchemaOnly);
            options.EnforceScriptingOptions = true;

            // start
            options.AnsiPadding = false;
            options.AppendToFile = true;
            options.ContinueScriptingOnError = false;
            options.ConvertUserDefinedDataTypesToBaseType = false;
            options.WithDependencies = false;
            options.IncludeHeaders = true;
            options.IncludeScriptingParametersHeader = false;
            options.SchemaQualify = true;
            options.Bindings = false;
            options.NoCollation = true;
            options.Default = true;
            options.ScriptDrops = false;
            options.ScriptSchema = true;
            options.ExtendedProperties = true;
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.TargetDatabaseEngineType = Microsoft.SqlServer.Management.Common.DatabaseEngineType.Standalone;
            options.LoginSid = false;
            options.Statistics = false;
            options.ScriptData = true;
            options.ChangeTracking = false;
            options.DriAllConstraints = true;
            options.ScriptDataCompression = false;
            options.DriForeignKeys = true;
            options.FullTextIndexes = false;
            options.Indexes = false;
            options.DriPrimaryKey = true;
            options.Triggers = false;
            options.DriUniqueKeys = true;


            if (isSchemaOnly == true)
            {
                options.ScriptSchema = true;
                options.DriForeignKeys = true;
                options.SchemaQualify = true;
                options.SchemaQualifyForeignKeysReferences = true;
                options.DriAll = true;
                options.ScriptData = false;
            }
            else
            {
                options.ScriptSchema = false;
                options.ScriptData = true;
                options.DriAll = false;
            }

            var tableNames = GetTablesNames(schema, sourceDefaultConnection, true);

            int progressCounter = 1;
            foreach (var tbl in tableNames)
            {
                if (tbl.SchemaName == "HR" && tbl.TableName == "FileDatas" && isSchemaOnly == false)
                {
                    continue;
                }
                else
                {
                    dbs.Tables[tbl.TableName, tbl.SchemaName].EnumScript(options);
                }

                DisplayDebug(string.Format("Running migration schema's ({2}) main generated script | Total: {0} | Processing #: {1}", tableNames.Count, progressCounter, schema));

                progressCounter++;
            }
        }

        private IList<DatabaseTableDTO> GetTablesNames(string schema, string connectionName, bool isAscending)
        {
            string sql = string.Format(@"WITH cte (lvl, object_id, name, schema_Name) AS
                                          (SELECT 1, object_id, sys.tables.name, sys.schemas.name as schema_Name
                                           FROM sys.tables Inner Join sys.schemas on sys.tables.schema_id = sys.schemas.schema_id
                                           WHERE type_desc = 'USER_TABLE'
                                             AND is_ms_shipped = 0
                                           UNION ALL SELECT cte.lvl + 1, t.object_id, t.name, S.name as schema_Name
                                           FROM cte
                                           JOIN sys.tables AS t ON EXISTS
                                             (SELECT NULL FROM sys.foreign_keys AS fk
                                              WHERE fk.parent_object_id = t.object_id
                                                AND fk.referenced_object_id = cte.object_id )
                                           JOIN sys.schemas as S on t.schema_id = S.schema_id
                                           AND t.object_id <> cte.object_id
                                           AND cte.lvl < 30
                                           WHERE t.type_desc = 'USER_TABLE'
                                             AND t.is_ms_shipped = 0 )
                                        SELECT schema_Name, name, MAX (lvl) AS dependency_level
                                        FROM cte
                                        where schema_Name = '{0}'
                                        GROUP BY schema_Name, name
                                        ORDER BY dependency_level {1},schema_Name, name;", schema, isAscending == true ? "ASC" : "DESC");

            var connectionString = ConfigurationManager.ConnectionStrings[connectionName];

            var listOfTables = new List<DatabaseTableDTO>();

            using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
            {
                conn.Open();
                using (var command = new SqlCommand(sql, conn))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            listOfTables.Add(new DatabaseTableDTO { SchemaName = schema, TableName = reader.GetString(1) });
                        }
                    }
                }
            }

            return listOfTables;
        }

        public class DatabaseTableDTO
        {
            public string SchemaName { get; set; }

            public string TableName { get; set; }
        }