Creating Complex Test Databases - Generating One Table for each of the 2,397 supported Collations

As a follow up to my series on complex test databases, this article will cover one of the more interesting test databases that I recently created. 


Goal: Create a database that includes one table for each supported collation. Each table contains a single column with the various column level collations supported by SQL Server 2012 (nearly 2,400 different collations supported).


The first step here is to determine where we can find the exhaustive list of supported collations. The answer comes from the built-in table-valued function, fn_helpcollations, that returns the list of supported collations in SQL Server 2012.  Once we have the exhaustive list of supported collations, we need to determine how we will leverage this list to create one table for each collation. If we were to do this manually, we would write out the following CREATE TABLE statements:

create table T1 (c1 nvarchar(50) collate Albanian_100_BIN)

create table T2 (c1 nvarchar(50) collate Albanian_100_BIN2)

create table T3 (c1 nvarchar(50) collate Albanian_100_CI_AI)

create table T2395 (c1 nvarchar(50) collate Yakut_100_CS_AS_KS)

create table T2396 (c1 nvarchar(50) collate Yakut_100_CS_AS_KS_WS)

create table T2397 (c1 nvarchar(50) collate Yakut_100_CS_AS_WS)


The repetition of these statements makes them good candidates for scripting using T-SQL. By leveraging the ROW_NUMBER function as the table numeric identifier, we're able to put together the following statement:


SELECT'create table T'+cast(ROW_NUMBER()over (orderbyname)aschar(10))+'(c1 nvarchar(50) collate '+name+')'



The output of this statement is exactly the 2,397 CREATE TABLE scripts needed to create our database. Copy the results and run them in an empty DB to create our desired database.

By leveraging the same language constructs, we could also create different flavors of this same DB. 

Example 1: 1 table with 1024 columns of different collations 
Example 2: Modify the script to produce one CREATE DATABASE script for each collation

Hope you enjoy,
Sam Lester (MSFT)