A stored proc to copy table data including any default value or binding data via a SQL script running within SQL Server

I would like to be able to copy a table and it's data and also still have any default value or binding (as it is labelled within SQL Server Management console) constraints copied over.

The script below is a testing script to demonstrate the idea. The last line I assume needs to be replaced with a call to a custom stored proc?

Note: The source table (aSourceTbl) schema varies and can change over time.

--TEST SETUP 
--Delete the prev tables so test script can be replayed
IF OBJECT_ID('aSourceTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceTbl;

IF OBJECT_ID('aSourceCopyTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceCopyTbl;

--Simple table 开发者_如何学Pythonto demonstrate table copying does not carry over the table constraits
CREATE TABLE [dbo].[aSourceTbl](
    [aValue] [int] NOT NULL,
    [DELETED] [int] NOT NULL
) ON [PRIMARY]

--Add some dummy data
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (1,2);
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (3,4);

--Add constraints of default values of 0 in this case 
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_aValue]  DEFAULT ((0)) FOR [aValue]
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_DELETED]  DEFAULT ((0)) FOR [DELETED]

--Actual Required SQL script from here down 
--The line below works nicely but does not copy the 2 constraints from the lines above into the new table. 
--TODO QUESTION: Replace line below with the same functionaility + the constraints are also copied into new table
Select * INTO aSourceCopyTbl FROM aSourceTbl

Could you please help me by suggesting a suitable stored proc that can replace the last line in above SQL snippet? Any help greatly appreciated :)

References:

• Similar SO Question however focuses on PK constraints. I am only interested in default value constraints in this case.

You can execute this code after the last row which will replicate the defauld constraints to the new table (replace the variables with your table names).

declare @table_name sysname, @new_table sysname, @cmd varchar(max)
select @table_name = 'SOURCE_TABLE', @cmd = '', @new_table = 'TEST_TABLE'


select @cmd = @cmd+'ALTER TABLE '+@new_table+' ADD CONSTRAINT [DF_' +@new_table+'_'+a.name+'] DEFAULT '+b.definition+' FOR['+a.name+']; 
' 
from sys.columns a 
join sys.default_constraints b on a.object_id = b.parent_object_id and a.column_id = b.parent_column_id
where a.object_id = object_id(@table_name)  

print @cmd
exec (@cmd)