SQL Server – Copy Table Structure Excluding Nullability

For table variables there is not much you can do. However, for temporary tables (even local ones -- i.e. those with names starting with a single #) there is quite a bit that can be done.

With regards to the behavior of local temporary tables not surviving the end of the process in which they were created, the place where people seem to get stuck is in assuming that they can only be created. That might be true for table variables (which is why they won't help here), but not so true for temporary tables (local or global): they can also be altered ! And, because local temporary tables are available to sub-processes, and because any changes made to them in a sub-process survive the ending of that sub-process, they can be created in the "current" scope and then altered in a sub-process. The end result will be the desired structure in the current scope :-) !

So you don't need to approach this problem in terms of needing to create the local temporary table with the perfect structure, just so long as there is a programmatic means of determining what changes should be made. And for this particular request that is (thankfully) not terribly difficult. Once the temporary table is created, we just look at the columns of the source table to get their names, datatypes, and optional collation (the three pieces needed for the ALTER TABLE statement). Then we just construct a Dynamic SQL string with the ALTER TABLE statements that adjust the nullability as desired. Execute that and all will be right with the world.

Set up the test:

For this test we will use a local temporary table as the table to copy, but that is just to make for an easier test, and a permanent table will behave the same way.

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#SourceTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #SourceTable...';
  DROP TABLE #SourceTable;
END;

CREATE TABLE #SourceTable
(
  Col1 INT,
  Col2 NVARCHAR(37) COLLATE Hungarian_100_CI_AI_SC NOT NULL,
  Col3 DATE,
  Col4 TIME(3) NOT NULL,
  Col5 FLOAT(20) NOT NULL,
  Col6 DECIMAL(10,3) NOT NULL,
  Col7 NUMERIC(14)
);

IF (OBJECT_ID(N'tempdb..#TargetTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #TargetTable...';
  DROP TABLE #TargetTable;
END;

Run the test:

Finding the nullability is rather easy. But you can't issue an ALTER COLUMN statement without specifying the datatype. The datatype is provided in proper format by the sys.dm_exec_describe_first_result_set() DMF, but that was introduced in SQL Server 2012, hence it won't help on SQL Server 2008 R2. So, we have to rebuild the actual datatypes, which means you need to: divide max_length by 2 for NCHAR and NVARCHAR; use scale by itself for time, datetime2, and datetimeoffset; and use both scale and precision for decimal and numeric ( <sarcasm> so much fun </sarcasm> ).

NOTE: Please remember to remove the two references to tempdb. in the code below when using this outside of a testing scenario. Those references -- noted in inline comments in the code -- are only there due to the source table (the table to be copied) being a temporary table and existing in tempdb.

SELECT *
INTO   #TargetTable
FROM   #SourceTable
WHERE  1 = 0;

DECLARE @AlterQuery NVARCHAR(MAX);
SET @AlterQuery = N'';

SELECT  @AlterQuery = @AlterQuery
        + N'ALTER TABLE #TargetTable ALTER COLUMN [' + col.[name] + N'] '
        + CASE
            WHEN col.user_type_id IN (34, 35, 36, 40, 48, 52, 56, 58, 59, 60, 61, 62, 98,
                            99, 104, 122, 127, 128, 129, 130, 189, 241, 256) THEN tp.name
            WHEN col.system_type_id = 240 THEN tp.name -- User-Defined Types (UDTs)
            WHEN col.user_type_id IN (231, 239) THEN tp.name + N'(' +
                       CONVERT(NVARCHAR(5), col.max_length / 2) + N')' -- nvarchar, nchar
            WHEN col.user_type_id IN (165, 167, 173, 175) THEN tp.name + N'(' +
                    CONVERT(NVARCHAR(5), col.max_length) + N')' -- (var)binary, (var)char
            WHEN col.user_type_id IN (41, 42, 43) THEN tp.name + N'(' +
                CONVERT(NVARCHAR(5), col.scale) + N')' -- time, datetime2, datetimeoffset
            WHEN col.user_type_id IN (106, 108) THEN tp.name + N'(' + 
                            CONVERT(NVARCHAR(5), col.[precision]) + N', ' +
                            CONVERT(NVARCHAR(5), col.scale) + N')' -- decimal, numeric
          END
        + ISNULL(N' COLLATE ' + col.collation_name, N'')
        + N' NULL;' + NCHAR(0x0D) + NCHAR(0x0A) -- CR + LF
FROM     tempdb.sys.columns col -- remove "tempdb."
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE    col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable') -- remove "tempdb..#"
AND      col.is_identity <> 1 -- exclude the IDENTITY field
AND      col.is_nullable = 0 -- only ALTER the columns that actually need it
ORDER BY col.column_id ASC;

PRINT @AlterQuery; -- debug

EXEC (@AlterQuery);

Check the results:

The following query will display the columns and their datatypes and nullability. You will see that all fields now have is_nullable showing as 1 :-).

SELECT col.column_id,
       col.[name],
       tp.[name],
       col.max_length,
       col.[precision],
       col.scale,
       col.is_nullable
FROM   tempdb.sys.columns col
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE  col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable')
ORDER BY col.column_id ASC;