Create an empty table from an existing table keeping the default value constraint

Question

I tried:

Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2

but the default value constraint is not created.

I have a smalldatetime column whose default value is getdate().

Answer 1

Nothing wrong with quick and easy short-cuts.

it just takes a bit of work. ;-)

See https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query

Create a user defined function using the code from the link, then you can exec the output...

snip...
select @tsql = dbo.udf_GenerateTableCreateScript(N'dbo.mytable')
exec @tsql