public CreateTableStatement Convert(CTable table) { var parser = new TSql120Parser(false); string[] parts = { table.Schema.SchemaName, table.TableName }; var createTable = new CreateTableStatement(); ///set schema and table name createTable.SchemaObjectName = new SchemaObjectName(); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = table.Schema.SchemaName }); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = $"[{table.TableName}]" }); //add columns createTable.Definition = new TableDefinition(); foreach (var col in table.Column) { if (col.ColumnType == DbType.Object) { continue; } var dataType = new SqlDataTypeReference { SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw) }; if (col.ColumnLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = col.ColumnLength.ToString() }); } var column = new ColumnDefinition { ColumnIdentifier = new Identifier { Value = col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, table.ConvertToSnakeCase) }, DataType = dataType }; if (!string.IsNullOrEmpty(col.DefaultValue)) { IList <ParseError> errors; //var defaultValueText = "CONVERT([char](32), REPLACE(CONVERT([char](36),NEWID()),'-',''))"; var scriptDefault = parser.ParseExpression(new StringReader(col.DefaultValue), out errors); column.DefaultConstraint = new DefaultConstraintDefinition { Expression = scriptDefault }; } if (col.IsIdentity) { column.IdentityOptions = new IdentityOptions { IdentitySeed = new IntegerLiteral { Value = col.ColumnType == System.Data.DbType.Byte ? "1" : "1000" }, IdentityIncrement = new IntegerLiteral { Value = "1" } } } ; column.Constraints.Add(new NullableConstraintDefinition { Nullable = col.IsNullable }); if (col.IsUnique) { column.Constraints.Add(new UniqueConstraintDefinition()); } if (col.IsIndexed) { column.Index = new IndexDefinition { Name = new Identifier { Value = $"IX_{col.ColumnName}" }, IndexType = new IndexType { IndexTypeKind = IndexTypeKind.NonClustered } } } ; createTable.Definition.ColumnDefinitions.Add(column); } //add PK's var pks = table.GetPrimaryKeyColumns(); if (pks.Count > 0) { var primaryKeyConstraint = new UniqueConstraintDefinition { IsPrimaryKey = true }; primaryKeyConstraint.Clustered = true; // todo: use metadata foreach (var pk in table.GetPrimaryKeyColumns()) { var columnIdentifier = new MultiPartIdentifier(); columnIdentifier.Identifiers.Add(new Identifier { Value = pk.ColumnName }); var columnRefExpression = new ColumnReferenceExpression { MultiPartIdentifier = columnIdentifier }; var columnWithSortOrder = new ColumnWithSortOrder { Column = columnRefExpression }; primaryKeyConstraint.Columns.Add(columnWithSortOrder); } var pkConstraintName = $"PK_{table.Schema.SchemaName}_{table.TableName}_{pks.First().ColumnName}"; primaryKeyConstraint.ConstraintIdentifier = new Identifier { Value = pkConstraintName }; createTable.Definition.TableConstraints.Add(primaryKeyConstraint); } //add foreign keys foreach (var col in table.Column) { if (col.ForeignKeyColumn == null || col.ForeignKeyColumn.Count == 0) { continue; } foreach (var fk in col.ForeignKeyColumn) { var fkConstraintName = $"FK_{table.TableName}_{fk.Table.TableName}"; var foreignKeyConstraint = new ForeignKeyConstraintDefinition(); foreignKeyConstraint.ConstraintIdentifier = new Identifier { Value = fkConstraintName }; foreignKeyConstraint.Columns.Add(new Identifier { Value = col.ColumnName }); foreignKeyConstraint.ReferenceTableName = new SchemaObjectName(); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.Schema.SchemaName }); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.TableName }); foreignKeyConstraint.ReferencedTableColumns.Add(new Identifier { Value = fk.ColumnName }); createTable.Definition.TableConstraints.Add(foreignKeyConstraint); } } return(createTable); } } }