Copy table structure excluding nullability into local temporary table
1
vote
3
answers
834
views
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:
- Copy complete structure of a table
- Create table structure from existing table
Asked by neizan
(113 rep)
Nov 12, 2015, 02:04 PM
Last activity: Apr 7, 2025, 12:46 PM
Last activity: Apr 7, 2025, 12:46 PM