Configure for user-defined datatypes

swirl80

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.

Brian Donahue

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".

<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
	<generator
		type="RedGate.SQLDataGenerator.ExpressionGenerators.Regexp.RegexpGenerator"
		name="YN"
		description="Y or N"
		category="Personal">
		<property name="Expression">[YN]</property>
		<matches field=".*YesNo" score="10" minlen="1" maxlen="1" />
		<type type="string"/>
	</generator>
</generators>

Hopefully this helps.

swirl80

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

swirl80

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

Aliraza125

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.