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:
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 #tt1SELECT 'CREATE TABLE AllDataTypes ('
-- Insert one row for each nullable data-type (system or user-defined)INSERT INTO #tt1SELECT '[' + name + 'Null] [' + name + '] NULL,'FROM sys.types WHERE is_nullable = 1UNION-- 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 parenthesisINSERT INTO #tt1SELECT '[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)