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