T-SQL: Create Table DDL Script

 in Database Administration (DBA) / T-SQL  tagged DBA / DDL / Script / T-SQL by

A T-SQL script to export the definition of a table may be used in an automation. For example when I automate an import of a data into the database, I don’t create a staging table with NVARCHAR(MAX) columns, but export the structure of the destination table as a staging table.

Create database.

-- Create database
USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

-- Create schema
CREATE SCHEMA pl;
GO

-- Create test table
IF (OBJECT_ID('PL.pl.Table 1', 'U') IS NOT NULL)
	BEGIN DROP TABLE PL.pl.[Table 1] END;
GO

CREATE TABLE PL.pl.[Table 1]
	(
		-- Exact Numerics
		[BIGINT] BIGINT
		, [BIT] BIT
		, [DECIMAL] DECIMAL
		, [INT] INT
		, [MONEY] MONEY
		, [NUMERIC] NUMERIC
		, [SMALLINT] SMALLINT
		, [SMALLMONEY] SMALLMONEY
		, [TINYINT] TINYINT
		 
		-- Approximate Numerics 
		, [FLOAT] FLOAT
		, [REAL] REAL
		 
		-- Date and Time 
		, [DATE] DATE
		, [DATETIME] DATETIME
		, [DATETIME2] DATETIME2
		, [DATETIMEOFFSET] DATETIMEOFFSET
		, [SMALLDATETIME] SMALLDATETIME
		, [TIME] TIME

		-- Character Strings 
		, [CHAR] CHAR
		, [VARCHAR] VARCHAR
		, [TEXT] TEXT
		 
		-- Unicode Character Strings 
		, [NCHAR] NCHAR
		, [NVARCHAR] NVARCHAR
		, [NTEXT] NTEXT
		 
		-- Binary Strings 
		, [BINARY] BINARY
		, [VARBINARY] VARBINARY
		, [IMAGE] IMAGE
		 
		-- Other Data Types 
		--, [CURSOR ] CURSOR 
		, [GEOGRAPHY] GEOGRAPHY
		, [GEOMETRY] GEOMETRY
		, [HIERARCHYID] HIERARCHYID
		, [SQL_VARIANT] SQL_VARIANT
		--, [TABLE] TABLE
		, [SYSNAME] SYSNAME
		, [TIMESTAMP] TIMESTAMP
		, [UNIQUEIDENTIFIER] UNIQUEIDENTIFIER
		, [XML] XML
		
		-- PL test columns
		, [IDENTITY] BIGINT IDENTITY(30000, 1200)
		, [VARCHAR 25] VARCHAR(25)
		, [Computed] AS (DATEADD(DAY, -(DAY([DATETIME NOT NULL]) - 1), [DATETIME NOT NULL]))
		, [Computed PERSISTED] AS ([TINYINT] + [SMALLINT]) PERSISTED
		, [DATETIME NOT NULL] DATETIME NOT NULL
		, [INT_NOT_NULL] INT NOT NULL
		, [BIT_NOT_NULL] BIT NOT NULL
		, [CHAR_2] CHAR(2)
		, [VARCHAR_MAX] VARCHAR(MAX)
		, [NVARCHAR_MAX] NVARCHAR(MAX)
		, [VARCHAR_25_COLLATION] VARCHAR(25) COLLATE Japanese_XJIS_100_CI_AS_KS
		, [NVARCHAR_11_COLLATION] NVARCHAR(11) COLLATE Chinese_Traditional_Pinyin_100_CI_AS_KS_WS
		, [VARBINARY_MAX] VARBINARY(MAX)
		, [FLOAT_2] FLOAT(2)
		, [NUMERIC_12_8] NUMERIC(12, 8)
		, [DECIMAL_18_6] DECIMAL(18, 6)
		, [UNIQUEIDENTIFIER_NOT_NULL] UNIQUEIDENTIFIER NOT NULL
		, [MONEY_NOT_NULL] MONEY NOT NULL
		, [SMALLMONEY_NOT_NULL] SMALLMONEY NOT NULL
	)
	ON [PRIMARY];
GO

-- Insert fake data into the test table
INSERT PL.pl.[Table 1]
	(
		[BIGINT]
		, [BIT]
		, [DECIMAL]
		, [SMALLMONEY_NOT_NULL]
		, [DATETIME NOT NULL]
		, [INT_NOT_NULL]
		, [BIT_NOT_NULL]
		, [UNIQUEIDENTIFIER_NOT_NULL]
		, [MONEY_NOT_NULL]
		, [SYSNAME]
	)
SELECT
	123
	, NULL
	, 12.3
	, 123.45
	, CONVERT(CHAR(10), GETDATE(), 120)
	, 10
	, 0
	, CAST('B14BC077-F1DF-457C-9F7E-7CB9E0BC1CF3' AS UNIQUEIDENTIFIER)
	, 321.66
	, 'SYSNAME fake data 1';
GO

-- Check the test table
SELECT *
FROM PL.pl.[Table 1];
GO

Script to export table.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Input Variables - Start
DECLARE
	@Source NVARCHAR(392) -- Database.Schema.Table
	, @Destination NVARCHAR(392)

SELECT
	@Source = N'PL.pl.Table 1'
	, @Destination = N'myNewTable'
-- Input Variables - Start

-- Split @Source - Start
DECLARE
	@SourceDatabaseName SYSNAME
	, @SourceSchemaName SYSNAME
	, @SourceTableName SYSNAME;

SELECT
	@SourceDatabaseName = LTRIM(RTRIM(PARSENAME(@Source, 3)))
	, @SourceSchemaName = LTRIM(RTRIM(PARSENAME(@Source, 2)))
	, @SourceTableName = LTRIM(RTRIM(PARSENAME(@Source, 1)));

IF (ISNULL(@SourceDatabaseName, '') = '') BEGIN SELECT @SourceDatabaseName = DB_NAME(); END
IF (ISNULL(@SourceSchemaName, '') = '') BEGIN SELECT @SourceSchemaName = N'dbo'; END
-- Split @Source - End

-- Missing @SourceTableName - Start
IF (ISNULL(@SourceTableName, '') = '')
	BEGIN
		PRINT N'Parameter @Source is not valid --> '
			+ 'Database: ' + @SourceDatabaseName
			+ '; Schema: ' + @SourceSchemaName
			+ '; Table: ' + CASE WHEN (ISNULL(@SourceTableName, '') = '') THEN '(blank)' END;
		RETURN;
	END
-- Missing @SourceTableName - End

-- Populate @ObjectID - Start
DECLARE
	@ObjectID INT
	, @SQL_@ObjectID NVARCHAR(MAX);

SELECT @SQL_@ObjectID = N'
SELECT @ObjectIDOut = O.[object_id]
FROM
	' + QUOTENAME(@SourceDatabaseName) + N'.sys.objects AS O
	JOIN ' + QUOTENAME(@SourceDatabaseName) + N'.sys.schemas AS S
		ON O.[schema_id] = S.[Schema_id]
WHERE
	O.[type] = ''U''
	AND S.[name] = N''' + @SourceSchemaName + N'''
	AND O.[name] = N''' + @SourceTableName + N''';
';

EXEC sp_executesql
	@SQL_@ObjectID
	, N'@ObjectIDOut INT OUTPUT'
	, @ObjectIDOut = @ObjectID OUTPUT;
-- Populate @ObjectID - End

-- @ObjectID does not exist - Start
IF (ISNULL(@ObjectID, '') = '')
	BEGIN
		PRINT
			QUOTENAME(@SourceDatabaseName)
				+ CHAR(46) + QUOTENAME(@SourceSchemaName)
				+ CHAR(46) + QUOTENAME(@SourceTableName)
				+ N' doesn''t exist';
		RETURN;
	END
-- @ObjectID does not exist - End

-- Split @Destination - Start
DECLARE
	@DestinationDatabaseName SYSNAME
	, @DestinationSchemaName SYSNAME
	, @DestinationTableName SYSNAME;

SELECT
	@DestinationDatabaseName = LTRIM(RTRIM(PARSENAME(@Destination, 3)))
	, @DestinationSchemaName = LTRIM(RTRIM(PARSENAME(@Destination, 2)))
	, @DestinationTableName = LTRIM(RTRIM(PARSENAME(@Destination, 1)));

IF (ISNULL(@DestinationDatabaseName, '') = '') BEGIN SELECT @DestinationDatabaseName = DB_NAME(); END
IF (ISNULL(@DestinationSchemaName, '') = '') BEGIN SELECT @DestinationSchemaName = N'dbo'; END
-- Split @Destination - End

-- Missing @DestinationTableName - Start
IF (ISNULL(@DestinationTableName, '') = '')
	BEGIN
		PRINT N'Missing table name in parameter @Destination';
		RETURN;
	END
-- Missing @DestinationTableName - Start

-- Script Table - Start
DECLARE @SQL NVARCHAR(MAX);
DECLARE
	@SQL_Columns NVARCHAR(MAX)
	, @SQL_ColumnsOut NVARCHAR(MAX);
		
-- Delete old destination table 		
SELECT @SQL = N'-- Delete old destination table'
	+ CHAR(13) + CHAR(10)
	+ N'IF (OBJECT_ID('''
	+ QUOTENAME(@DestinationDatabaseName)
	+ CHAR(46) + QUOTENAME(@DestinationSchemaName)
	+ CHAR(46) + QUOTENAME(@DestinationTableName)
	+ N''') IS NOT NULL)'
	+ CHAR(13) + CHAR(10)
	+ N'	BEGIN'
	+ N' DROP TABLE '
	+ QUOTENAME(@DestinationDatabaseName)
	+ CHAR(46) + QUOTENAME(@DestinationSchemaName)
	+ CHAR(46) + QUOTENAME(@DestinationTableName)
	+ N';'
	+ N' END
	'
	+ CHAR(13) + CHAR(10);
			
-- CREATE TABLE
SELECT @SQL = @SQL
	+ N'-- CREATE TABLE'
	+ CHAR(13) + CHAR(10)
	+ N'CREATE TABLE '
	+ QUOTENAME(@DestinationDatabaseName)
	+ CHAR(46) + QUOTENAME(@DestinationSchemaName)
	+ CHAR(46) + QUOTENAME(@DestinationTableName)
	+ N'
(' + CHAR(13) + CHAR(10);

SELECT @SQL_Columns = N'
SELECT @ColumnsOut = 
	STUFF((
			SELECT R.R
			FROM
				(
					SELECT
						CASE
							WHEN C.column_id = 1 THEN CHAR(9) + ''''
							ELSE CHAR(9) + '', ''
						END -- [New Line Start]
						+ QUOTENAME(C.[name]) -- [Column Name]
						+ CASE
							WHEN C.[is_computed] = 1
								THEN '' AS '' + CC.[definition] + CASE WHEN CC.[is_persisted] = 1 THEN '' PERSISTED'' ELSE '''' END
							ELSE
								+ '' '' + CASE WHEN T.is_user_defined = 1 THEN QUOTENAME(SCHEMA_NAME(T.schema_id))
									+ ''.'' + QUOTENAME(T.[name]) ELSE QUOTENAME(UPPER(T.[name])) END -- [Type] TODO Chesk if there are any custom defined types and export create scripts for them.
								+ CASE
									WHEN (T.[name] IN (''decimal'', ''numeric'') AND NOT (C.[precision] = 18 AND C.[scale] = 0))
										THEN '' ('' + CAST(C.[precision] AS VARCHAR) + '', '' + CAST(C.[scale] AS VARCHAR) + '')''
									WHEN (T.[name] IN (''float'') AND C.[precision] != 53)
										THEN '' ('' + CAST(C.[precision] AS VARCHAR) + '')''
									WHEN (T.[name] IN (''char'', ''varchar'') AND C.[max_length] != 1)
										THEN '' ('' + REPLACE(CAST(C.[max_length] AS VARCHAR), ''-1'', ''MAX'') + '')''
									WHEN (T.[name] IN (''nchar'', ''nvarchar'') AND C.[max_length] != 2)
										THEN '' ('' + REPLACE(CAST(CAST(C.[max_length] AS FLOAT) / CAST(2 AS FLOAT) AS VARCHAR), ''-0.5'', ''MAX'') + '')''
									WHEN (T.[name] IN (''varbinary'') AND C.[max_length] != 1)
										THEN '' ('' + REPLACE(CAST(C.[max_length] AS VARCHAR), ''-1'', ''MAX'') + '')''
									ELSE ''''
								END -- [Precision, Scale]
								+ CASE
									WHEN (C.[collation_name] IS NOT NULL AND T.is_user_defined != 1
										/*AND C.collation_name != (SELECT CONVERT(VARCHAR, SERVERPROPERTY(''collation'')))*/
										/*Always export collation, because we do not know where (on which server with what settings) the table will be created*/
									)
										THEN '' COLLATE '' + C.[collation_name]
									ELSE ''''
								END -- [Collation]
								+ CASE WHEN C.[is_identity] = 1 THEN '' IDENTITY ('' + CAST(I.[seed_value] AS VARCHAR) + '', ''
									+ CAST(I.[increment_value] AS VARCHAR) + '')'' ELSE '''' END -- [Identity]
								+ CASE WHEN C.[is_rowguidcol] = 1 THEN '' ROWGUIDCOL'' ELSE '''' END -- [RowGuIDCol]
								+ CASE WHEN C.[is_nullable] = 1 THEN '''' ELSE '' NOT NULL'' END -- [Nullable]
						END -- [Computed]
						+ CHAR(13) + CHAR(10) -- [New Line End]
						AS R
						, C.[column_id] AS ID
					FROM
						' + QUOTENAME(@SourceDatabaseName) + '.sys.columns AS C
						LEFT JOIN ' + QUOTENAME(@SourceDatabaseName) + '.sys.types AS T
							ON C.[user_type_id] = T.[user_type_id]
						LEFT JOIN ' + QUOTENAME(@SourceDatabaseName) + '.sys.computed_columns AS CC
							ON C.[object_id] = CC.[object_id]
							AND C.[column_id] = CC.[column_id]
						LEFT JOIN ' + QUOTENAME(@SourceDatabaseName) + '.sys.identity_columns AS I
							ON C.[object_id] = I.[object_id]
							AND C.[column_id] = I.[column_id]
						--LEFT JOIN ' + QUOTENAME(@SourceDatabaseName) + '.sys.default_constraints AS DC
						--	ON C.[object_id] = DC.[parent_object_id]
						--	AND C.[column_id] = DC.[parent_column_id]
					WHERE C.[object_id] = ' + CAST(@ObjectID AS VARCHAR) + '
				) AS R
			ORDER BY ID
			FOR XML PATH(''''), ROOT(''Root''), TYPE
		).value(''/Root[1]'', ''NVARCHAR(MAX)''), 1, 0, ''''
	)
'

EXEC sp_executesql
	@SQL_Columns
	, N'@ColumnsOut NVARCHAR(MAX) OUTPUT'
	, @ColumnsOut = @SQL_ColumnsOut OUTPUT;

SELECT @SQL = @SQL + ISNULL(@SQL_ColumnsOut, '') + ');
'
-- Script Table - End

-- Show the script - Start
PRINT @SQL;
-- Show the script - Start
GO

The preliminry steps check the variables @Source and @Destination:

  • @Source – blank source table and not existing source table
  • @Destination – blank destination table

Based on @SourceSchemaName and @SourceTableName I pick @ObjectID and use it as an identifier of the scripted table.

Next I delete a destination table if it exists. A hint: manipulate the backend processes on a separate server/database/schema.

CREATE TABLE script simply joins the system tables where the information is and wraps the joined result with CASE statements.
I use a Dynamic SQL to point to the exact object (Database, Schema, Table).

Delete Database.

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

This script will be modified constantly in order to be as accurate as possible. Please be adviced that the responsability for your code is yours.

Any suggestions are welcomed.

Keep it simple :-)