将表数据生成SQL脚本的存储过程
1
CREATE PROCEDURE dbo.UspOutputData
2
@tablename sysname
3
AS
4
declare @column varchar(1000)
5
declare @columndata varchar(1000)
6
declare @sql varchar(4000)
7
declare @xtype tinyint
8
declare @name sysname
9
declare @objectId int
10
declare @objectname sysname
11
declare @ident int
12![](/Images/OutliningIndicators/None.gif)
13
set nocount on
14
set @objectId=object_id(@tablename)
15![](/Images/OutliningIndicators/None.gif)
16
if @objectId is null -- 判斷對象是否存在
17
begin
18
print 'The object not exists'
19
return
20
end
21
set @objectname=rtrim(object_name(@objectId))
22![](/Images/OutliningIndicators/None.gif)
23
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
24
begin
25
print 'object not in current database'
26
return
27
end
28![](/Images/OutliningIndicators/None.gif)
29
if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table
30
begin
31
print 'The object is not table'
32
return
33
end
34![](/Images/OutliningIndicators/None.gif)
35
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
36![](/Images/OutliningIndicators/None.gif)
37
if @ident is not null
38
print 'SET IDENTITY_INSERT '+@TableName+' ON'
39![](/Images/OutliningIndicators/None.gif)
40
declare syscolumns_cursor cursor
41![](/Images/OutliningIndicators/None.gif)
42
for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
43![](/Images/OutliningIndicators/None.gif)
44
open syscolumns_cursor
45
set @column=''
46
set @columndata=''
47
fetch next from syscolumns_cursor into @name,@xtype
48![](/Images/OutliningIndicators/None.gif)
49
while @@fetch_status < >-1
50
begin
51
if @@fetch_status < >-2
52
begin
53
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
54![](/Images/OutliningIndicators/None.gif)
55
begin
56
set @column=@column+case when len(@column)=0 then'' else ','end+@name
57![](/Images/OutliningIndicators/None.gif)
58
set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
59
end
60![](/Images/OutliningIndicators/None.gif)
61
+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
62
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
63
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
64
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
65
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
66
else @name end
67![](/Images/OutliningIndicators/None.gif)
68
end
69![](/Images/OutliningIndicators/None.gif)
70
end
71![](/Images/OutliningIndicators/None.gif)
72
fetch next from syscolumns_cursor into @name,@xtype
73![](/Images/OutliningIndicators/None.gif)
74
end
75![](/Images/OutliningIndicators/None.gif)
76
close syscolumns_cursor
77
deallocate syscolumns_cursor
78![](/Images/OutliningIndicators/None.gif)
79
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
80![](/Images/OutliningIndicators/None.gif)
81
print '--'+@sql
82
exec(@sql)
83![](/Images/OutliningIndicators/None.gif)
84
if @ident is not null
85
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
86![](/Images/OutliningIndicators/None.gif)
87
GO
88![](/Images/OutliningIndicators/None.gif)
89
exec UspOutputData 你的表名
90![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)