Create custom generators

You can create custom generators from a predefined template or from scratch in the following categories: Business, Health, Location, IT, Payment, Personal, and Product.

Create a custom template-based generator

dbForge Studio for SQL Server comes with a wide collection of generator templates that you can use to create custom generators.

To create a custom template-based generator:

1. Navigate to C:\Users\Public\Documents\Devart\dbForge Studio for SQL Server\Data Generators\Templates.

Note

The default location for storing custom data generation settings is C:\Users\Public\Documents\Devart\dbForge Studio for SQL Server\Data Generators. This default location can’t be changed.

However, you can specify a custom location for .dgen files by modifying the registry at Computer\HKEY_CURRENT_USER\Software\Devart\dbForge Studio for SQL Server\Folders.

2. In any editor, open the template .xml file you want to use to create a generator.

Click to open the example of the template. 
<?xml version="1.0" encoding="utf-8"?>
<Generators xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Generator>
    <Id>c93d2ba9-3fcb-4638-8b86-53eb7164f0e0</Id>
    <Category>Example Template</Category>
    <Name>DateTimeOffsetGenerator</Name>
    <ShortName>DateTimeOffsetGenerator</ShortName>
    <Description>fill with date and time that has time zone awareness values</Description>
    <Type>DateTimeOffsetGenerator</Type>
    <ColumnTypes>
      <Type>datetimeoffset</Type>
    </ColumnTypes>
    <Matches>
      <Match table=".*" column="*" priority="10" />
    </Matches>
    <Options>
      <RowsCountToGenerate>10</RowsCountToGenerate>
      <AllowNulls>true</AllowNulls>
      <NullProportion>10</NullProportion>
      <Unique>false</Unique>
      <IsAlwaysUnique>false</IsAlwaysUnique>
      <UseDefault>false</UseDefault>
      <ValueDistributionMode>Seed</ValueDistributionMode>
      <SeedValue>1</SeedValue>
      <SequentialValueTicks>864000000000</SequentialValueTicks>
      <DateTimeRange>MinMaxDate</DateTimeRange>
      <OffsetFromColumnMinDays>0</OffsetFromColumnMinDays>
      <OffsetFromColumnMaxDays>7</OffsetFromColumnMaxDays>
      <OffsetFromColumnMinTime />
      <OffsetFromColumnMaxTime />
      <MinDate>1970-01-01T00:00:00</MinDate>
      <MaxDate>2015-09-17T14:17:00.1991805+03:00</MaxDate>
      <IsRandomizeDate>true</IsRandomizeDate>
      <IsRandomizeTime>true</IsRandomizeTime>
      <MinFraction>0</MinFraction>
      <MaxFraction>999</MaxFraction>
      <MaxAllowedFraction>999</MaxAllowedFraction>
      <IsRandomizeFraction>true</IsRandomizeFraction>
      <MinTimeZone>-5</MinTimeZone>
      <MaxTimeZone>5</MaxTimeZone>
      <IsRandomizeZone>true</IsRandomizeZone>
      <MaxTime>863990000000</MaxTime>
      <MinTime>10000000</MinTime>
      <Scale>7</Scale>
      <Precision>34</Precision>
    </Options>
  </Generator>
</Generators>

For more information about XML tags and matching rules, see Explanation of tags and Matching rules.

3. Make the changes.

4. Save the file to C:\Users\Public\Documents\Devart\dbForge Studio for SQL Server\Data Generators.

5. Restart dbForge Studio.

Create a custom generator from scratch

The following example uses the Regular Expression generator.

To create a custom generator:

1. Navigate to C:\Users\Public\Documents\Devart\dbForge Studio for SQL Server\Data Generators.

2. In this folder, create an .xml file or copy an existing .xml file.

3. Enter the following code. Replace the values with your actual data.

<?xml version="1.0" encoding="utf-8"?>
<Generators xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Generator>
    <Category>IT</Category>
    <Name>Enter a generator name here</Name>
    <ShortName>Enter a short name here</ShortName>
    <Description>Enter a description here</Description>
    <Type>SqlRegularExpressionGenerator</Type>
    <Language>DE</Language>
    <ColumnTypes>
      <Type>varchar</Type>
      <Type>char</Type>
    </ColumnTypes>
    <Matches>
      <!-- table: any table -->
      <Match table=".*"              column="^VIN$"        priority="120" minlen="17" />
      <!-- table: exact word -->
      <Match table="language"        column="^code$"       priority="99"  minlen="2" />

      <!-- table: contains substring -->
      <Match table=".*Product.*"     column=".*Name.*"     priority="90"  minlen="9" />

      <!-- table: ends with a word -->
      <Match table=".*Category"      column="name"         priority="100" minlen="5" />

      <!-- table: starts with a word -->
      <Match table="PLU.*"           column=".*code"       priority="100" minlen="5" />

      <!-- table: starts with a word + ends with a word-->
      <Match table="PLU.*codes"      column=".*"           priority="50"  minlen="5" />

      <!-- table: alternation (a|b) -->
      <Match table=".*(ship|deliv).*" column="Track.*Num"  priority="120" minlen="15" />

      <!-- table: exact word (without anchors) -->
      <Match table="ProductDescription" column="Description" priority="80" minlen="100" maxlen="199" />


      <!-- ═══════════════════════════════════════════════════════════════════
           COLUMN: column name patterns
           ═══════════════════════════════════════════════════════════════════ -->

      <!-- column: exact name ^ $, uppercase -->
      <Match table=".*"   column="^VIN$"         priority="120" minlen="17" />

      <!-- column: exact name ^ $, lowercase -->
      <Match table=".*"   column="^id$"          priority="99"  minlen="2" />

      <!-- column: exact word without anchors, uppercase -->
      <Match table=".*"   column="ISBN"          priority="30"  minlen="13" />

      <!-- column: exact word without anchors, lowercase -->
      <Match table=".*"   column="language"      priority="99"  minlen="2" />

      <!-- column: ends with a word (without .*) -->
      <Match table=".*"   column="Qty$"          priority="90" />

      <!-- column: ends with a word (with .*) -->
      <Match table=".*"   column=".*Description" priority="10"  minlen="40" maxlen="99" />

      <!-- column: contains words on both sides (.*word.*) -->
      <Match table=".*"   column=".*Name.*"      priority="90"  minlen="9" />

      <!-- column: starts + ends -->
      <Match table=".*"   column="Lang.*Code"    priority="89"  minlen="2" />

      <!-- column: starts + wildcard-suffix -->
      <Match table=".*"   column="VIN.*num.*"    priority="120" minlen="17" />

      <!-- column: contains A + ends with B -->
      <Match table=".*"   column=".*Produc.*Name" priority="100" minlen="9" />

      <!-- column: any column (.*) -->
      <Match table="PLU.*codes" column=".*"      priority="50"  minlen="5" />


      <!-- ═══════════════════════════════════════════════════════════════════
           LENGTH: combinations of minlen / maxlen attributes
           ═══════════════════════════════════════════════════════════════════ -->

      <!-- minlen only: column wider than minimum -->
      <Match table=".*"   column="ISBN"          priority="30"  minlen="13" />

      <!-- minlen = maxlen: fixed column length -->
      <Match table=".*"   column="ISSN"          priority="20"  minlen="8"  maxlen="8" />

      <!-- minlen + maxlen: column length range -->
      <Match table=".*"   column="ISBN"          priority="30"  minlen="10" maxlen="12" />

      <!-- no length restrictions (numeric types) -->
      <Match table=".*"   column="ISBN"          priority="30" />
 
    </Matches>
    <Options>
      <Expression>Your regular expression should be here</Expression>
    </Options>
  </Generator>
</Generators>

4. Save the .xml file.

5. Restart dbForge Studio.

Explanation of tags

The following table describes the tags used in XML files for generators.

Tag Description
<Category></Category> Specifies a category a custom generator is added to.
<Name></Name> Specifies the name of the generator you want to add.
<ShortName></ShortName> Specifies the generator name to be displayed in the Preview pane.
<Description></Description> Specifies the generator description.
<Type></Type> Specifies the base generator type used to create the custom generator.
<Language></Language> Specifies the language assigned to a generator based on the user’s locale. Available options are United States (default), Australia, Canada, Germany, and United Kingdom.
<Matches></Matches> Defines the matching rules.
<ColumnTypes></ColumnTypes> Specifies the column data types applicable to the generator.
<Options></Options> Contains tags that specify additional generator options.

Matching rules

The following table describes the matching rules for the <Matches> tag.

Value Description
table="" Specifies the name of the matched tables.
column="" Specifies the name of the matched columns.
* Indicates any number of characters.
^ Indicates a strict beginning of a name. For example, "^Test.*" means that the beginning of the name must be "Test", followed by any characters. The valid names may be test, testing, or Test123.
$ Indicates a strict end of a name. For example, ".*Test$" means that the end of the name must be "Test", preceded by any characters. The valid names may be test, myTest, or 123test.
| Indicates the OR operator.
priority="n" Specifies the priority, which determines how a generator is automatically assigned to a column. Higher values take precedence over lower values. For custom generators, use a value greater than 4, because default data type generators use a priority range of 1 to 4.
minlen="n" Specifies the minimum allowed column length. The parameter is ignored for varchar(max), nvarchar(max), text, ntext, image, and varbinary(max).
maxlen="n" Specifies the maximum column length. The parameter is ignored for varchar(max), nvarchar(max), text, ntext, image, and varbinary(max).
The parameter is optional.