Copy table structure excluding nullability into local temporary table

Question

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, keys, indexes, etc. I'll also note that this must be done programmatically within a procedure, so I can't just generate a create table script and modify it. The best I've come up with has been:

select top 0 *
into #MyTempTable
from dbo.MyTable

which copies the nullability of each field, thus defeating the purpose for me.

I've also played around with dynamic SQL and extracting the column data from table INFORMATION_SCHEMA.COLUMNS to build the create table statement, but the issue there is that the temp table goes out of scope after the dynamic SQL statement executes and control is returned to the "main" procedure. (And I'd rather not jam the rest of the procedure into the dynamic SQL statement.) If there were a way to return the temp table from the exec (@Sql) statement or keep it in scope somehow, it might work, but I don't know that there's a way to do that.

Related questions:

Solomon Rutzky · Accepted Answer · 2015-11-13T16:56:48.577

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;

score 1 · Answer 2 · answered Nov 12 '15 at 15:44

If you really really need to make your life very difficult down the road, you may be able to create something like the following code. It creates a temporary table, inserts the top(10) rows into it from the source table, then outputs those rows from the temporary table.

The temporary table is created with all columns set to allow 'NULL' values.

Create the stored proc (various lines have been wrapped to avoid horizontal scroll bars):

SET NOCOUNT ON;
USE tempdb;
GO
CREATE PROCEDURE dbo.NonNullTest
(
    @SourceTableSchema SYSNAME
    , @SourceTableName SYSNAME
)
AS
BEGIN
    DECLARE @cmdCreateTable NVARCHAR(MAX);  
    DECLARE @cmdPopulateTable NVARCHAR(MAX);
    DECLARE @cmdSelectFromTable NVARCHAR(MAX);
    DECLARE @cmd NVARCHAR(MAX);
    DECLARE @ObjectID INT;
    DECLARE @msg NVARCHAR(1000);

    SELECT @ObjectID = o.object_id
    FROM sys.objects o 
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE s.name = @SourceTableSchema 
        AND o.name = @SourceTableName;

    IF @ObjectID IS NOT NULL
    BEGIN
        SET @cmdCreateTable = '';
        --Create the list of columns with their type, as NULLable
        SELECT @cmdCreateTable = @cmdCreateTable 
            + CASE WHEN @cmdCreateTable = '' 
                THEN '' 
                ELSE ', ' 
            END 
            + c.name + ' ' + t.name + ' NULL'
        FROM sys.columns c 
            INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
        WHERE c.object_id = @ObjectID
        ORDER BY c.column_id;
        --assemble the CREATE TABLE statement
        SET @cmdCreateTable = 'CREATE TABLE ' 
            + QUOTENAME(@SourceTableSchema) 
            + '.' + QUOTENAME('#' + @SourceTableName) 
            + '(' + @cmdCreateTable + ');';
        --INSERT TOP(10) into a temporary table
        SET @cmdPopulateTable = 'INSERT INTO ' 
            + QUOTENAME(@SourceTableSchema) + '.' 
            + QUOTENAME('#' + @SourceTableName) 
            + ' SELECT TOP(10) * FROM ' 
            + QUOTENAME(@SourceTableSchema) + '.' 
            + QUOTENAME(@SourceTableName) + ';';
        --SELECT from the temporary table
        SET @cmdSelectFromTable = 'SELECT * FROM ' 
            + QUOTENAME(@SourceTableSchema) + '.' 
            + QUOTENAME('#' + @SourceTableName) + ';'
        --assemble the full dynamic SQL statement and execute it
        SET @cmd = @cmdCreateTable + @cmdPopulateTable + @cmdSelectFromTable;
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @msg = N'Could not locate object ' 
        + @SourceTableSchema + '.' + @SourceTableName;
        RAISERROR (@msg, 15, 1)
    END
END
GO

Here we test the stored proc:

CREATE TABLE dbo.SomeTable
(
    ID INT NOT NULL
);

INSERT INTO dbo.SomeTable
SELECT o.object_id
FROM sys.objects o;

EXEC dbo.NonNullTest @SourceTableSchema = 'dbo', @SourceTableName = 'SomeTable';

And, the results:

score 1 · Answer 3 · edited Apr 07 '25 at 12:46

1

For SQL Server 2012 and later you can use sys.dm_exec_describe_first_result_set to get the table definition of a query.

edited Apr 07 '25 at 12:46

Tom Mayfield

103
4

answered Nov 12 '15 at 16:22

JohnG

1,093
2
12
27

Copy table structure excluding nullability into local temporary table

3 Answers3