Copy column defaults from one table to new table

Copying the structure of an existing table to a new table is a simple matter. It can be done as follows:

SELECT top 0 * 
into my_ulti.wrk_Misc_CreateTemp
from my_trxlg.wrk_Misc

However, this method will not copy the column defaults to the new table. The following displays a mechanism to copy the column defaults over to the new table.

-- declare a temporary table variable to hold our default column information.
declare @tempDefaults table (
       colname sysname
       , defaultname sysname
       , [definition] varchar(100)
       , [suffix] int not null default(0)
)

declare @sourceTableName varchar(100), @destTableName varchar(100)
select @sourceTableName = 'my_trxlg.wrk_Misc'
       , @destTableName = 'my_ulti.wrk_Misc_CreateTemp'

-- insert our data into our table variable.
insert into @tempDefaults
(colname, defaultname, [definition])
select --top 100 
       colname = a.[name]
       , defaultname = 'DF_wrk_Misc_' + a.[name]
       , b.definition
from sys.columns a
inner join sys.default_constraints b on a.default_object_id = b.[object_id]
where a.[object_id] = object_id(@sourceTableName)
       and a.default_object_id != 0

-- ensure that the name of the default constraint is unique.
WHILE (exists(select 1 from @tempDefaults a inner join sys.objects b on a.defaultname = b.[name]))
begin
       UPDATE a set
              suffix = suffix + 1
              , defaultname = 'DF_wrk_Misc_' + a.colname + '_' + convert(varchar, suffix + 1)
       from @tempDefaults a
       inner join sys.objects b on a.defaultname = b.[name]
end

-- declare & initialize a variable that will hold our command statement.
declare @sql varchar(max)
set @sql = ''

-- build our sql command statement that will be executed.
select @sql = @sql
       + 'ALTER TABLE ' + @destTableName + ' ADD CONSTRAINT ' + a.defaultname + ' default ' + a.[definition] + ' for ' + a.colname 
       + char(13) + char(10) -- + 'go' + char(13) + char(10)
from @tempDefaults a

-- print out our sql statement
print @sql
-- execute our sql statement
exec (@sql)