This is some sample code to create a duplicate table. I know this is easily done within SSMS by generating a create script. However, I intend to use this within a program that automates the process of backing up tables and their data; and also restoring them. The goal is to produce something usable when full database backups are unnecessary and unwieldy.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
-- TABLE BACKUP SCRIPT USE AdventureWorks2012 DECLARE @fieldName SYSNAME, @typeName SYSNAME, @length INT, @precision INT, @scale INT, @collation SYSNAME, @isNullable INT, @nextLine VARCHAR(MAX) DECLARE myCursor CURSOR FOR SELECT [name] AS [FieldName] ,TYPE_NAME(system_type_id) AS [TypeName] ,[max_length] AS [max_length] ,[precision] AS [precision] ,[scale] AS [scale] ,[collation_name] AS [collation_name] ,[is_nullable] AS [is_nullable] FROM sys.columns WHERE object_id = object_id(N'Sales.SpecialOffer') ORDER BY column_id OPEN myCursor FETCH NEXT FROM myCursor INTO @fieldName, @typeName, @length, @precision, @scale, @collation, @isNullable PRINT 'CREATE TABLE Sales.SpecialOfferBackup (' WHILE @@FETCH_STATUS = 0 BEGIN SELECT @nextLine = '[' + @fieldName + '] [' + @typeName + ']' + CASE WHEN @typeName in ('nvarchar') THEN '(' + CAST(@length / 2 AS VARCHAR(20)) + ')' WHEN @typeName in ('varchar') THEN '(' + CAST(@length AS VARCHAR(20)) + ')' WHEN @typeName in ('decimal','numeric') THEN '(' + CAST(@precision AS VARCHAR(20)) + ',' + CAST(@scale AS VARCHAR(20)) + ')' ELSE '' END + CASE WHEN @isNullable = 1 THEN ' NULL' ELSE ' NOT NULL' END FETCH NEXT FROM myCursor INTO @fieldName, @typeName, @length, @precision, @scale, @collation, @isNullable IF @@FETCH_STATUS = 0 PRINT @nextLine + ',' ELSE PRINT @nextLine END PRINT ')' CLOSE myCursor DEALLOCATE myCursor |