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 may be what you’re looking for.
|
1 2 3 4 5 6 7 8 9 |
-- this part creates the temp table with exactly the same structure as the original one into #CLIENTS where 1 = 0 -- this part sets all the constraints on the new table from the original table declare @newTable sysname set @originalTable = 'CLIENTS' set @sqlCommand = '' 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 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.