Have you ever wanted to create a copy of a database table, programmatically?
Could be a part of a stored procedure as a temporary table, or just a quick way of duplicating structure and constraints of the original one.
If so, this Transact-SQL script maybe what you’re looking for.
-- this part creates the temp table with exactly the same structure as the original one
select top 1 *
where 1 = 0
-- this part sets all the constraints on the new table from the original table
declare @originalTable sysname
declare @newTable sysname
declare @sqlCommand varchar(max)
set @originalTable = 'CLIENTS'
set @newTable = '#CLIENTS'
set @sqlCommand = ''
select @sqlCommand = @sqlCommand+'ALTER TABLE ' + @newTable +' ADD CONSTRAINT [DF_' +@newTable+'_'+sc.name+'] DEFAULT '+dc.definition+' FOR['+sc.name+']; '
from sys.columns sc join sys.default_constraints dc on sc.object_id = dc.parent_object_id and sc.column_id = dc.parent_column_id
where sc.object_id = object_id(@originalTable)
This T-SQL first generates a new table with the same fields as the source table and after that adds the constraints with a dynamic SQL query.