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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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]; 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 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.
1 2 3 4 5 6 7 8 9 |
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 :-)