1

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:

  • 1
    Let me guess, you think writing one stored procedure for multiple tables is a great thing? I would suggest you re-think that, and write a single stored proc for each table, and likely one for each individual DML action you want to take against those tables. I know this goes against the object-oriented code paradigm you're used to, but for an RDBMS it just works far more reliably and results in far more maintainable code down the road. – Max Vernon Nov 12 '15 at 15:06
  • Look at Brent Ozar's excellent post on the 5 stages of dynamic sql for details. – Max Vernon Nov 12 '15 at 15:07
  • @MaxVernon I never said anything about "one stored procedure for multiple tables" and I understand the differences between writing procs in an RDBMS and OOP ("that I'm used to"...do you know me?). I mentioned dynamic SQL just as one avenue considered in my search to find a solution to my question, which is how to copy a table structure excluding nullability. If there's a non-dynamic-SQL solution out there, that would be preferable. – neizan Nov 12 '15 at 15:37
  • Point taken, @neizan - I didn't mean to imply anything negative. Just want to ensure you know that avenue is fraught with nightmares. – Max Vernon Nov 12 '15 at 15:46
  • @MaxVernon no problem, and thanks for your efforts in your answer. – neizan Nov 12 '15 at 15:57
3

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;
  • 1
    I started writing an answer myself an hour or so ago, then got pulled away. "My answer" was quite similar to the idea you've elaborated on here. That is, create the table, then alter it to have all columns nullable instead of creating as nullable immediately. Also, your rules for rebuilding the data types are more complete than mine were. I'm going to give this a shot. Thanks! – neizan Nov 12 '15 at 21:34
1

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:

enter image description here

  • Thanks. In your test code, after the EXEC ... line, and assuming it's called within another proc, how can #SomeTable be accessed to, say, insert some records into it. It goes out of scope as soon as control is returned to the calling proc, no? – neizan Nov 12 '15 at 15:55
  • Correct. This code shows that you'll need to do all the work that touches the temp table inside a single piece of dynamic sql. – Max Vernon Nov 12 '15 at 15:57
  • Right. When I was exploring the dynamic-SQL solution, this is about as far as I got, then I realized that the temp table was only in scope within the dynamic SQL execution (doh!). I mentioned in my question that I'd rather not jam the rest of the procedure into the dynamic SQL statement (because I realize how the complexity will begin to explode, and I don't want to go down that road). It's looking more and more like I'm not going to be able to do what I want. – neizan Nov 12 '15 at 16:02
  • 1
    Perhaps if you explain why you are attempting to do this, you'll get an answer outside what you've already explored. – Max Vernon Nov 12 '15 at 16:14
  • Perhaps you could do it without creating a temporary table? Perhaps you could use a regular table that you dynamically create, use, then delete afterwards? – Max Vernon Nov 12 '15 at 16:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.