T-SQL: Create Table DDL Script

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

-- Create database

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

-- Create schema

CREATE SCHEMA pl;

-- Create test table

IF (OBJECT_ID('PL.pl.Table 1', 'U') IS NOT NULL)

BEGIN DROP TABLE PL.pl.[Table 1] END;

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];

-- 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';

-- Check the test table

SELECT *

FROM PL.pl.[Table 1];

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

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

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

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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

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 :-)

Peter Lalovsky

Keep it simple

Keep it simple

T-SQL: Create Table DDL Script

Leave a comment Cancel reply