T-SQL Random Data Generator

I’ve often had the need to populate database tables with chunks of random data for testing and so forth and this random data generator stored procedure does the trick for me. I’ve had it knocking round in my sock draw for quite a while so I’ve dusted it off and updated it. It actually does a bit more than I remembered. I’ve used it on SQL Server 2005 and 2008 and the INFORMATION_SCHEMA use should make it forwardly compatible.

Code

usp_RandomInsertGenerator

This procedure actually does the heavy lifting and generates the insert statements.

usp_RandomInsertGeneratorWrapper

It’s very easy to create a wrapper stored procedure to get multiple statements. One row of random data isn’t that much use – 1000 might be.

Usage

To get an insert for one line of data

To get multiple insert statements call the wrapper function

Notes and disclaimers

What it does

  • It outputs an insert T-SQL statement for any named table in the database.
  • Detects foreign keys and inserts and makes a random but valid link to the child table (this was the hard part)
  • Detect different data types and does the necessary SQL formatting
  • Accounts for max character length in string type fields
  • Accounts for identity columns
  • Detects nullable columns and leaves them null

What it doesn’t do

  • Doesn’t account for unique indexes
  • There are omitted datatypes (cursor, timestamp, binary, varbinary, image) that I’m not really interested in right now.

Hacks and shortcuts

Where the foreign key links are linked to a child table via a unique index rather than a primary key it assumes a foreign key of 1. The INFORMATION_SCHEMA views don’t include index information. So this would necessitate delving into the sys.index table. Not impossible but the proc was complicated enough as it was. I didn’t need it so I didn’t do it.

Assumptions

  1. Foreign keys are of a numeric type i.e. int, float et. al.
  2. Foreign key constraints exist on the database. It isn’t magic – it can’t work out the relationships if they don’t exist.

Room for improvement

Here’s some extensions that may be useful for individual projects

  1. Implement missing datatypes.
  2. Create custom inserts for known field names to give more realistic data for your application
  3. Randomise the character data properly.

So that’s it. Live it, love it and fiddle about with it a bit.

Useful links

The official documentation for the INFORMATION_SCHEMA used in this procedure is here
https://msdn.microsoft.com/en-us/library/ms186778.aspx

As ever the source code is on GitHub. There is nothing more than what is here but my WordPress code formatting plugin hasn’t made a great job of formatting the SQL code so the GitHub link might give clearer view of the SQL. Also, it seemed cruel not to give it a home on my demo repository with the rest of my code samples.
https://github.com/timbrownls20/Demo/blob/master/T-SQL/usp_RandomInsertGenerator.sql

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *