Today we’ll take a look into how we can replicate the source system schema with Biml and SQL Server DDL statements.
No, this isn’t about replicating data as you tend to do with SQL Server. This is how to mirror the source system schema for scenarios when you’d like to have a staging area that is a 100% copy of the source system.
There are a couple of ways to do this and I’ll list 3 of them.
This example assumes you already have collections defined inside RootNode.Tables.
If not, you can use this SQL Server script to create a table containing most of the datatypes available for SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AllDataTypes]( [bigintNotNull] [bigint] NOT NULL, [bigintNull] [bigint] NULL, [binaryNotNull] [binary](1) NOT NULL, [binaryNull] [binary](1) NULL, [bitNotNull] [bit] NOT NULL, [bitNull] [bit] NULL, [charNotNull] [char](1) NOT NULL, [charNull] [char](1) NULL, [dateNotNull] [date] NOT NULL, [dateNull] [date] NULL, [datetime2NotNull] [datetime2](7) NOT NULL, [datetime2Null] [datetime2](7) NULL, [datetimeNotNull] [datetime] NOT NULL, [datetimeNull] [datetime] NULL, [datetimeoffsetNotNull] [datetimeoffset](7) NOT NULL, [datetimeoffsetNull] [datetimeoffset](7) NULL, [decimalNotNull] [decimal](18, 0) NOT NULL, [decimalNull] [decimal](18, 0) NULL, [floatNotNull] [float] NOT NULL, [floatNull] [float] NULL, [geographyNotNull] [geography] NOT NULL, [geographyNull] [geography] NULL, [geometryNotNull] [geometry] NOT NULL, [geometryNull] [geometry] NULL, [hierarchyidNotNull] [hierarchyid] NOT NULL, [hierarchyidNull] [hierarchyid] NULL, [imageNotNull] [image] NOT NULL, [imageNull] [image] NULL, [intNotNull] [int] NOT NULL, [intNull] [int] NULL, [moneyNotNull] [money] NOT NULL, [moneyNull] [money] NULL, [ncharNotNull] [nchar](1) NOT NULL, [ncharNull] [nchar](1) NULL, [ntextNotNull] [ntext] NOT NULL, [ntextNull] [ntext] NULL, [numericNotNull] [numeric](18, 0) NOT NULL, [numericNull] [numeric](18, 0) NULL, [nvarcharNotNull] [nvarchar](1) NOT NULL, [nvarcharNull] [nvarchar](1) NULL, [realNotNull] [real] NOT NULL, [realNull] [real] NULL, [smalldatetimeNotNull] [smalldatetime] NOT NULL, [smalldatetimeNull] [smalldatetime] NULL, [smallintNotNull] [smallint] NOT NULL, [smallintNull] [smallint] NULL, [smallmoneyNotNull] [smallmoney] NOT NULL, [smallmoneyNull] [smallmoney] NULL, [sql_variantNotNull] [sql_variant] NOT NULL, [sql_variantNull] [sql_variant] NULL, [sysnameNotNull] [sysname] NOT NULL, [textNotNull] [text] NOT NULL, [textNull] [text] NULL, [timeNotNull] [time](7) NOT NULL, [timeNull] [time](7) NULL, [timestampNotNull] [timestamp] NOT NULL, [tinyintNotNull] [tinyint] NOT NULL, [tinyintNull] [tinyint] NULL, [uniqueidentifierNotNull] [uniqueidentifier] NOT NULL, [uniqueidentifierNull] [uniqueidentifier] NULL, [varbinaryNotNull] [varbinary](1) NOT NULL, [varbinaryNull] [varbinary](1) NULL, [varcharNotNull] [varchar](1) NOT NULL, [varcharNull] [varchar](1) NULL, [xmlNull] [xml] NULL, [xmlNotNull] [xml] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
1. BimlExtension Method: GetDropAndCreateDdl
This method allows you to output a predefined template DDL statement as a string.
Pros: The DDL template is predefined and you simple call “tableNode.GetDropAndCreateDdl()” to retrieve it.
Cons: Template is not easily manipulated.
Code nugget:
|
1 2 3 |
<#foreach (var tableNode in RootNode.Tables) { #> <#=tableNode.GetDropAndCreateDdl()#> <# } #> |
Preview window inside BimlStudio:
2. User defined BimlScript:
This method allows you to output and control the DDL statement as a string. Alter as needed.
Pros: DDL template is user defined and it’s up to you how you’d like to output it.
Cons: Code nugget can get out of hand if you need to handle lots of arguments. Should be used in a CallBimlScript file.
Code nugget:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
<#@ import namespace="System.Text" #> <#@ import namespace="Varigence.Biml.CoreLowerer.TSqlEmitter" #> <#="SET ANSI_NULLS ON"#> <#="GO" #> <#="SET QUOTED_IDENTIFIER ON"#> <#="GO" #> <#="" #> <#="------USER DEFINED TEMPLATE------" #> <# foreach (var Table in RootNode.Tables) { #> <#="IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[Frysdisken].[" + Table.Name + "]') AND type IN (N'U'))"#> <#="DROP TABLE [Frysdisken].[" + Table.Name + "]"#> <#="GO" #> <#="" #> <#="CREATE TABLE [Frysdisken].[" + Table.Name + "]"#> <#="(" #> <#StringBuilder sb = new StringBuilder(); #> <# foreach (var Column in Table.Columns) { #> <# sb.AppendLine ( Column.QualifiedName + " " + TSqlTypeTranslator.Translate(Column) + Column.IsNullable.ToString().Replace("True", " NULL").Replace("False", " NOT NULL") + "," ); #> <# } #> <# sb.Length-=3; #> <#=sb.ToString() #> <#=")" #> <# } #> |
Preview window inside BimlStudio:
3. User defined BimlScript:
This method adds “Annotations” when you import a schema using “GetDatabaseSchema” to your “Tables” collections.
The “Annotations” can be used the same way as the previous sample. Simply change the properties into the suitable Annotations tag.
Pros: Earliest definition of data types when imported with “GetDatabaseSchema” method. Other methods shown might contain data type conversions that aren’t what you’d like to be seen done.
Cons: Code nugget can get out of hand if you need to handle lots of arguments. Should be used in a CallBimlScript file.
Code nugget:
|
1 2 3 4 5 6 7 8 9 10 |
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Tables> <# var importedSchema = RootNode.Connections["Connection1"].GetDatabaseSchema(null, null, ImportOptions.None, true, null); #> <# foreach (var tableNode in importedSchema.TableNodes) { #> <#=tableNode.GetBiml()#> <# } #> </Tables> </Biml> |
Preview window inside BimlStudio:
