Category Archives: cursors

cursors, metadata queries, T-SQL

Creating a duplicate table

Leave a comment

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