Configure for user-defined datatypes

The simplest way to populate your database with test data.

Configure for user-defined datatypes

Postby swirl80 » Wed Jul 09, 2014 10:04 pm

A system developed by a third party has a char udt (udtYesNo) used on a number of columns which will contain a Y or N.

Is it possible to configure data generator to say that any column which uses this udt will use [YN]. At present I believe I'd have to alter every one of these columns because by default it is choosing the regular expression [A-Z|0-9] which doesn't meet our needs.
Posts: 21
Joined: Thu Oct 23, 2008 3:26 pm

Postby Brian Donahue » Fri Jul 11, 2014 11:36 am

It is possible to set a different generator by default, by adding a generator configuration in the Config folder of SQL Data Generator. So if you open Notepad (as administrator, probably) and save the following content into c:\\program files (x86)\\red gate\\sql data generator 3\\Config\\YN.xml , that should cause SDG to populate a column with Y or N by default, when the column is 1 character in length and the name of the column ends in "YesNo".
Code: Select all
<?xml version="1.0" encoding="iso-8859-1"?>
      description="Y or N"
      <property name="Expression">[YN]</property>
      <matches field=".*YesNo" score="10" minlen="1" maxlen="1" />
      <type type="string"/>

Hopefully this helps.
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Postby swirl80 » Mon Jul 21, 2014 3:58 pm

Thanks Brian, that worked.

One thing to note, I could only get this to work on anything new I added to the schema (ie, I created a test table with a xxxxYesNo column), anything current seemed to ignore it. I tried refreshing the schema and closing / reloading SDG but still didn't pick it up.

In the end I created a new project using the same database and this then picked it up.....
Posts: 21
Joined: Thu Oct 23, 2008 3:26 pm

Postby swirl80 » Tue Jul 22, 2014 8:20 am

Is there a way to do this based on the name of a user defined data type as opposed to a column name?

For example instead of looking for columns *YesNo, is it possible to set this to look at all columns of datatype "udtYesNo"?

I can use the example provided by Brian for this particular column type but there are several other udt's but the naming of the column doesn't always follow standards...
Posts: 21
Joined: Thu Oct 23, 2008 3:26 pm

Re: Configure for user-defined datatypes

Postby Aliraza125 » Fri Dec 05, 2014 10:31 am

SQL Data Generator cannot leave a column in a table because it is designed to generate realistic test data, and not as a data sanitization application. Red Gate recognize a small market for this, and that is why the help article exists to help you accomplish that in a roundabout way.

SQL Data Generator has a hard-coded "server assigned" generator for IDENTITY columns that you cannot change because identity columns have to conform to the constraint on the server anyway (seed value, increment, and current value). The topic was primarily about that, so I was going to raise a feature request to allow "at your own risk" changes there if there is a need for it.
Get free demos for Testking MB7-701 exam and mcts exam with guaranteed success. Our best quality usuhs prepares you well before appearing in the final exams of and mcts with Quincy University
Posts: 1
Joined: Fri Dec 05, 2014 10:25 am

Return to SQL Data Generator 3

Who is online

Users browsing this forum: No registered users and 0 guests