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;
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.
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; }
}