MS SQL server 生成 insert 数据的存储过程脚本

1. CREATEPROCEDUREdbo.UspOutputData
2. @tablenamesysname
3. AS
4. declare@columnvarchar(1000)
5. declare@columndatavarchar(1000)
6. declare@sqlvarchar(4000)
7. declare@xtypetinyint
8. declare@namesysname
9. declare@objectIdint
10. declare@objectnamesysname
11. declare@identint
13. setnocounton
14. set@objectId=object_id(@tablename)
16. if@objectIdisnull--判断对象是否存在
17. begin
18. print'Theobjectnotexists'
19. return
20. end
21. set@objectname=rtrim(object_name(@objectId))
23. if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密
24. begin
25. print'objectnotincurrentdatabase'
26. return
27. end
29. ifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是table
30. begin
31. print'Theobjectisnottable'
32. return
33. end
35. select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80
37. if@identisnotnull
38. print'SETIDENTITY_INSERT'+@TableName+'ON'
40. declaresyscolumns_cursorcursor
42. forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid
44. opensyscolumns_cursor
45. set@column=''
46. set@columndata=''
47. fetchnextfromsyscolumns_cursorinto@name,@xtype
49. while@@fetch_status<>-1
50. begin
51. if@@fetch_status<>-2
52. begin
53. if@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理
55. begin
56. set@column=@column+casewhenlen(@column)=0then''else','end+@name
58. set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','
59. end
61. +casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,char
62. when@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar
63. when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime
64. when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime
65. when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier
66. else@nameend
68. end
70. end
72. fetchnextfromsyscolumns_cursorinto@name,@xtype
74. end
76. closesyscolumns_cursor
77. deallocatesyscolumns_cursor
79. set@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablename
81. print'--'+@sql
82. exec(@sql)
84. if@identisnotnull
85. print'SETIDENTITY_INSERT'+@TableName+'OFF'
87. GO

CREATEPROCEDUREdbo.UspOutputData
@tablenamesysname
AS
declare@columnvarchar(1000)
declare@columndatavarchar(1000)
declare@sqlvarchar(4000)
declare@xtypetinyint
declare@namesysname
declare@objectIdint
declare@objectnamesysname
declare@identint

setnocounton
set@objectId=object_id(@tablename)

if@objectIdisnull--判断对象是否存在
begin
print'Theobjectnotexists'
return
end
set@objectname=rtrim(object_name(@objectId))

if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密
begin
print'objectnotincurrentdatabase'
return
end

ifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是table
begin
print'Theobjectisnottable'
return
end

select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80

if@identisnotnull
print'SETIDENTITY_INSERT'+@TableName+'ON'

declaresyscolumns_cursorcursor

forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid

opensyscolumns_cursor
set@column=''
set@columndata=''
fetchnextfromsyscolumns_cursorinto@name,@xtype

while@@fetch_status<>-1
begin
if@@fetch_status<>-2
begin
if@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理

begin
set@column=@column+casewhenlen(@column)=0then''else','end+@name

set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','
end

+casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,char
when@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar
when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime
when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime
when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier
else@nameend

end

end

fetchnextfromsyscolumns_cursorinto@name,@xtype

end

closesyscolumns_cursor
deallocatesyscolumns_cursor

set@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablename

print'--'+@sql
exec(@sql)

if@identisnotnull
print'SETIDENTITY_INSERT'+@TableName+'OFF'

GO