What are the challenges you face when working across database platforms? Take the survey

Configure for user-defined datatypes

swirl80swirl80 Posts: 26
edited December 5, 2014 5:31AM in SQL Data Generator
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.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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"?>
    		description="Y or N"
    		<property name="Expression">[YN]</property>
    		<matches field=".*YesNo" score="10" minlen="1" maxlen="1" />
    		<type type="string"/>

    Hopefully this helps.
  • Options
    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.....
  • Options
    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...
  • Options
    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 testking.co.uk guaranteed success. Our best quality usuhs prepares you well before appearing in the final exams of www.bc.edu and mcts with Quincy University
Sign In or Register to comment.