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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- this part creates the temp table with exactly the same structure as the original one select top 1 * into #CLIENTS from CLIENTS 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) exec (@sqlCommand) |
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.