Creating Complex Test Databases - Creating a Table with all Supported SQL Server Data Types

  • Article

As part of my series on creating databases with interesting characteristics for testing purposes, today we'll leverage the internal SQL Server system tables to create a table containing columns of each nullable and non-nullable data types supported by SQL Server.

Goal: Create a table that contains one column of every nullable data type and one column for every non-nullable data type supported by SQL Server.

As with most of the test databases that we're creating in this series, it is fairly easy to understand how we might tackle this problem manually (if we had the time and patience), but sometimes it is a bit trickier to find a more elegant scripting solution.  In this case if we were to build these tables manually, we could open SSMS and use the table designer to create a table with all data types from the drop down menu item, once for nullable columns and once for non-nullable columns. 

We could manually walk through this exercise to create the nearly 70 system columns, but it is much more interesting to leverage the internal SQL Server system tables to do this programmatically, especially if the database contains several table types, assembly types, and other user-defined types, which would increase the manual effort.  The internal table that contains information about each system and user-defined type is the sys.types table. 

Leveraging the information stored in this system table, we can solve the problem by:

  1. Creating a temp table to store what will become our final scripted syntax
  2. Building an INSERT INTO statement to retrieve all nullable data types from sys.types
  3. Building an INSERT INTO statement to retrieve all non-nullable data types from sys.types

Based on this strategy, our syntax would be:

CREATE TABLE #tt1 (c1 VARCHAR(100)) -- Create a temp table to store one row for each line in our create table statement

-- Build the CREATE TABLE header
INSERT INTO #tt1
SELECT 'CREATE TABLE AllDataTypes ('

-- Insert one row for each nullable data-type (system or user-defined)
INSERT INTO #tt1
SELECT '[' + name + 'Null] [' + name + '] NULL,'
FROM sys.types WHERE is_nullable = 1
UNION
-- Insert one row for each non-nullable data-type (system or user-defined)
SELECT '[' + name + 'NotNull] [' + name + '] NOT NULL,'
FROM sys.types WHERE name <> 'xml'

-- Append our final row with proper syntax including the closing parenthesis
INSERT INTO #tt1
SELECT '[xmlNotNull] [xml] NOT NULL)'

--Return the desired CREATE TABLE syntax
SELECT c1 FROM #tt1

 

Running this query will produce the intended syntax below.  Notice that there are two data types in the list that are not nullable (timestamp and sysname).  Additional logic could be added to the script to create various precision, scale, and length values for supported data types to get additional coverage as this information is also contained within sys.types.

 CREATE TABLE AllDataTypes (
 [bigintNotNull] [bigint] NOT NULL,
 [bigintNull] [bigint] NULL,
 [binaryNotNull] [binary] NOT NULL,
 [binaryNull] [binary] NULL,
 [bitNotNull] [bit] NOT NULL,
 [bitNull] [bit] NULL,
 [charNotNull] [char] NOT NULL,
 [charNull] [char] NULL,
 [dateNotNull] [date] NOT NULL,
 [dateNull] [date] NULL,
 [datetime2NotNull] [datetime2] NOT NULL,
 [datetime2Null] [datetime2] NULL,
 [datetimeNotNull] [datetime] NOT NULL,
 [datetimeNull] [datetime] NULL,
 [datetimeoffsetNotNull] [datetimeoffset] NOT NULL,
 [datetimeoffsetNull] [datetimeoffset] NULL,
 [decimalNotNull] [decimal] NOT NULL,
 [decimalNull] [decimal] NULL,
 [floatNotNull] [float] NOT NULL,
 [floatNull] [float] NULL,
 [geographyNotNull] [geography] NOT NULL,
 [geographyNull] [geography] NULL,
 [geometryNotNull] [geometry] NOT NULL,
 [geometryNull] [geometry] NULL,
 [hierarchyidNotNull] [hierarchyid] NOT NULL,
 [hierarchyidNull] [hierarchyid] NULL,
 [imageNotNull] [image] NOT NULL,
 [imageNull] [image] NULL,
 [intNotNull] [int] NOT NULL,
 [intNull] [int] NULL,
 [moneyNotNull] [money] NOT NULL,
 [moneyNull] [money] NULL,
 [ncharNotNull] [nchar] NOT NULL,
 [ncharNull] [nchar] NULL,
 [ntextNotNull] [ntext] NOT NULL,
 [ntextNull] [ntext] NULL,
 [numericNotNull] [numeric] NOT NULL,
 [numericNull] [numeric] NULL,
 [nvarcharNotNull] [nvarchar] NOT NULL,
 [nvarcharNull] [nvarchar] NULL,
 [realNotNull] [real] NOT NULL,
 [realNull] [real] NULL,
 [smalldatetimeNotNull] [smalldatetime] NOT NULL,
 [smalldatetimeNull] [smalldatetime] NULL,
 [smallintNotNull] [smallint] NOT NULL,
 [smallintNull] [smallint] NULL,
 [smallmoneyNotNull] [smallmoney] NOT NULL,
 [smallmoneyNull] [smallmoney] NULL,
 [sql_variantNotNull] [sql_variant] NOT NULL,
 [sql_variantNull] [sql_variant] NULL,
 [sysnameNotNull] [sysname] NOT NULL,
 [textNotNull] [text] NOT NULL,
 [textNull] [text] NULL,
 [timeNotNull] [time] NOT NULL,
 [timeNull] [time] NULL,
 [timestampNotNull] [timestamp] NOT NULL,
 [tinyintNotNull] [tinyint] NOT NULL,
 [tinyintNull] [tinyint] NULL,
 [uniqueidentifierNotNull] [uniqueidentifier] NOT NULL,
 [uniqueidentifierNull] [uniqueidentifier] NULL,
 [varbinaryNotNull] [varbinary] NOT NULL,
 [varbinaryNull] [varbinary] NULL,
 [varcharNotNull] [varchar] NOT NULL,
 [varcharNull] [varchar] NULL,
 [xmlNull] [xml] NULL,
 [xmlNotNull] [xml] NOT NULL)

Hope you enjoy,
Sam Lester (MSFT)