1: USE தகவல்தளம் ;
2:
3: declare @tablename varchar(255)
4: set @tablename = 'test'
5:
6: declare @proceed varchar(20)
7:
8:
9: select @proceed = object_id(@tablename)
10:
11: select @proceed = [type] from sys.objects where name = @tablename
12:
13: if @proceed is null
14: BEGIN
15: print 'Table "' + @tablename + '" does not exist in this database. '
16: + char(13) + 'Please try changing the database name in the USE statement or
17: try correcting the table name.'
18: return
19: END
20:
21: if not @proceed = 'U'
22: BEGIN
23: print 'Object "' + @tablename + '" is not a table...'
24: return
25: END
26:
27: declare @structure table
28: (
29: colname varchar(1000),
30: coltype varchar(50),
31: colsize int
32: )
33:
34: insert into @structure
35: select sys.columns.name, sys.types.name, sys.columns.max_length
36: from sys.columns left join sys.types
37: on sys.columns.system_type_id = sys.types.system_type_id
38: where object_id = object_id(@tablename)
39:
40:
41: declare @declaration varchar(8000)
42: set @declaration = ''
43: select @declaration = @declaration + char(13) + '@' + colname
44: + ' ' + coltype + '(' +
45: convert(varchar(20) , colsize) + '),'
46: from @structure
47:
48: --Build Insert statement
49: declare @statement varchar(8000)
50:
51: set @statement = ''
52:
53: select @statement = @statement + char(13)
54:
55: SELECT @STATEMENT = @statement + 'INSERT INTO ' + @tablename + ' ('
56:
57: SELECT @STATEMENT = @statement + char(13) + colname + ',' from @structure
58:
59: SELECT @STATEMENT = @STATEMENT + ')'
60:
61: select @statement = replace(@statement, ',)', ')')
62:
63: select @statement = @statement + ' values('
64:
65:
66:
67: SELECT @STATEMENT = @STATEMENT + char(13) + '@' + colname + ',' from @structure
68:
69: select @statement = @statement + ')'
70:
71: select @statement = replace(@statement, ',)', ')')
72:
73:
74: select @declaration = substring(@declaration, 1, len(@declaration)-1)
75:
76: print 'CREATE PROCEDURE dbo.PROC1 ' + char(13) + '(' + char(13) + @declaration
77: + char(13) + ')' + char(13) + 'AS' + char(13)
78:
79: print char(13)
80: print @statement
81:
82: --Update
83: declare @updatestatement varchar(3000)
84:
85: set @updatestatement = 'UPDATE ' + @tablename + ' SET '
86:
87: select @updatestatement = @updatestatement + char(13) + ' ' + colname +
88: ' = @' + colname + ',' from @structure
89:
90: select @updatestatement = @updatestatement + ')'
91:
92: select @updatestatement = replace(@updatestatement, ',)', char(13) + ' WHERE 1=2')
93:
94: print char(13)
95:
96: print @updatestatement